473,836 Members | 1,935 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How can I format a datetime as 'yyyymmddhhnnss '?

I need to insert a user_date_time_ created field in the format
user_name + date_time_creat ed where the date_time_creat ed isn't one of
the standard formats but is yyyymmddhhnnss. I can get most of the way
there using CONVERT, but I can't stop e.g. 080705 hrs (8 hrs, 7
minutes and 5 seconds) on March 6, 2008 being returned as

20080306875 instead of
20080306080705.

How can I get those zeroes back in there?

--
Regards.
Richard.
Mar 6 '08 #1
2 3720
Am Thu, 06 Mar 2008 07:38:45 GMT schrieb Richard Sherratt:
I need to insert a user_date_time_ created field in the format
user_name + date_time_creat ed where the date_time_creat ed isn't one of
the standard formats but is yyyymmddhhnnss. I can get most of the way
there using CONVERT, but I can't stop e.g. 080705 hrs (8 hrs, 7
minutes and 5 seconds) on March 6, 2008 being returned as

20080306875 instead of
20080306080705.

How can I get those zeroes back in there?
Try this:
select convert(varchar ,getdate(),112) +
replace(convert (varchar,getdat e(),108),':','' )

bye,
Helmut
Mar 6 '08 #2
On Thu, 6 Mar 2008 14:14:20 +0100, Helmut Woess <us****@inode.a t>
wrote:
>Am Thu, 06 Mar 2008 07:38:45 GMT schrieb Richard Sherratt:
>I need to insert a user_date_time_ created field in the format
user_name + date_time_creat ed where the date_time_creat ed isn't one of
the standard formats but is yyyymmddhhnnss. I can get most of the way
there using CONVERT, but I can't stop e.g. 080705 hrs (8 hrs, 7
minutes and 5 seconds) on March 6, 2008 being returned as

20080306875 instead of
20080306080705 .

How can I get those zeroes back in there?

Try this:
select convert(varchar ,getdate(),112) +
replace(conver t(varchar,getda te(),108),':',' ')
Nice one.

Thanks, Helmut.

--
Regards.
Richard.
Mar 6 '08 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2777
by: Ted | last post by:
I would like to format my date "06/08/2003 12:00" to something like "1 day ago" or "24 hours ago". Is there a date format I can use to do this or does anyone have any pointers as to how i could do this. Any help much appreciated Thanks Ted
0
1490
by: Johan den Boer | last post by:
Hi How to format this datetime format 2003-11-09 08:58:59 to Sun, November 9 2003 8:58am
5
11543
by: cody | last post by:
Iam using Beta2 of VS2005 and getting the error "Error 4 'format-dateTime()' is an unknown XSLT function" at the following line: <xsl:value-of select="format-dateTime($pubDate,' : ')" /> how can I get around this?
4
18316
by: Vinitha | last post by:
Hi, I 've ("9/27/2003 12:11:31 PM") this value in database . i need to get only this value ("9/27/2003 " ) .How will i format it to get this value? It will be helpful if some one could point me to some code sample for the same thank you in advance
5
43551
by: Jack | last post by:
I am trying to format a DateTime object as mmddyyyy and NOT mm/dd/yyyy. What is the easiest way to do this?
1
1510
by: Doug Swanson | last post by:
I'm trying to get a datetime value (10/24/2001 6:00 AM) to display as 10/24/01 6:00. I know I could hard code some format but I'm trying to use the culture info etc. so that (and this is my understanding of it) if in the web.config I change the culture info to some country that disaplays as dd/mm/yy hh:mm then my app will display this in that manner. I can't seem to find any examples using the culture info...on a side note if I have a...
1
9491
by: oscar | last post by:
Hi expart, I have facing the problem of formatting the datetime in Dropdownlist. I am using Data Reader to retrive the date data from Sql2000 server and bind to DropDownList Control. The default format showing in the DropDownList is "MM/dd/yyyy". I want to change the format to "dd/MM/yyyy". The code sample as below. sqlconn.open() dim sqlcomm as sqlclient.sqlcommand
2
2183
by: JC Voon | last post by:
hi: Is there a function to format the datetime value to the following format 2005-09-29T17:57:03.0000000+08:00 ? currently i'm using a hard way Dim result As String
2
14512
by: Dabbler | last post by:
I can't figure out how to format a Label containing a datetime value in a GridView . <asp:TemplateField> <ItemTemplate> <asp:Label ID="JoinDate" runat="server" Text='<%# Eval("JoinDate") %>' ></asp:Label> </ItemTemplate> </asp:TemplateField> I use DataTextFormatString="{0:MMM yyyy}" in a DropDownList but this won't
5
2637
by: A.M | last post by:
Hi, I have a datetime value and want to format it to "June 1, 2006" shape. How can I do that? Thank you,
0
9810
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9656
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10819
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10570
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
5641
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5811
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4438
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4000
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3100
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.