469,590 Members | 2,464 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,590 developers. It's quick & easy.

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_created where the date_time_created 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 3565
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_created where the date_time_created 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,getdate(),108),':','')

bye,
Helmut
Mar 6 '08 #2
On Thu, 6 Mar 2008 14:14:20 +0100, Helmut Woess <us****@inode.at>
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_created where the date_time_created 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,getdate(),108),':','')
Nice one.

Thanks, Helmut.

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

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Johan den Boer | last post: by
4 posts views Thread by Vinitha | last post: by
5 posts views Thread by Jack | last post: by
1 post views Thread by Doug Swanson | last post: by
2 posts views Thread by JC Voon | last post: by
2 posts views Thread by Dabbler | last post: by
5 posts views Thread by A.M | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.