How can I format a datetime as 'yyyymmddhhnnss'?

[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?

--
Regards.
Richard.

[Helmut Woess]

Re: How can I format a datetime as 'yyyymmddhhnnss '?

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

[Richard Sherratt]

Re: How can I format a datetime as 'yyyymmddhhnnss '?

On Thu, 6 Mar 2008 14:14:20 +0100, Helmut Woess <user22@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.