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_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.

[Helmut Woess]

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

[Richard Sherratt]

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.