formating date to May 7, 2013 11:54 AM

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

formating date to May 7, 2013 11:54 AM

Author

Topic

nietzky
Yak Posting Veteran

75 Posts

Posted - 2013-05-07 : 11:56:33

Hello, I am trying to format a date to the following:

select CONVERT(nvarchar(30),GETDATE(),100)

returns:
May 7 2013 11:54AM

I would like to return:
May 7, 2013 11:54 AM

Can you please shed soem light on how to accomplish this ? Not familiar with CHARINDEX, SUBSTRING, RIGHT functions etc.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-07 : 12:36:27

You can use STUFF() function as follows:

[CODE]
select STUFF(STUFF(DateString, StrLength-1, 0, ' '), StrLength-12, 0, ',') as NewString1 from
(select CONVERT(nvarchar(30),GETDATE(),100) as DateString,
LEN(CONVERT(nvarchar(30),GETDATE(),100)) as StrLength) AS D1;
[/CODE]

Other styles are documented at: http://msdn.microsoft.com/en-us/library/ms187928.aspx

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-07 : 12:40:07

if this is required for display purposes in an application/report, better handle it on the front end of an application.

Cheers
MIK

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-05-07 : 12:45:49

Another variation of STUFF:

select STUFF(STUFF(CONVERT(nvarchar(30),GETDATE(),100), LEN(CONVERT(nvarchar(30),GETDATE(),100))-1, 0, ' '), charindex(' ',CONVERT(nvarchar(30),GETDATE(),100),(charindex(' ',CONVERT(nvarchar(30),GETDATE(),100))+3)), 0, ', ')

MIK 2008 has a good point.

djj

nietzky
Yak Posting Veteran

75 Posts

Posted - 2013-05-07 : 14:25:36

Thank you very much. Yes, I agree to implement this from the application side it would be better. Unfortunetly, we are creating automated report (plain HTML) and this way would give me a bit more control. Thx again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources