Convert date to format 2014_02_07

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

General SQL Server Forums

New to SQL Server Programming

Convert date to format 2014_02_07

Author

Topic

ranvir_2k
Posting Yak Master

180 Posts

Posted - 2014-02-07 : 06:41:31

Hi all,

I would like to convert today's date into the format:

YYYY_MM_DD
2014_02_07

I tried:

select convert(varchar(4),(datepart(yy,getdate())))+'_'+convert(varchar(2),(datepart(mm,getdate())))+'_'+convert(varchar(2),(datepart(dd,getdate())))

but it gave the date as:

2014_2_7 instead of 2014_02_07

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2014-02-07 : 06:55:21

[code]SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD][/code]

Replace getdate() with your date field

ranvir_2k
Posting Yak Master

180 Posts

Posted - 2014-02-07 : 07:05:40

Thanks for the reply but I need the format in YYYY_MM_DD, not YYYY-MM-DD, i.e. with an underscore.

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2014-02-07 : 07:05:42

Sorry i missed read that it was underscores you were trying to use and I think that you need to use the right function something like this:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/4315e068-0a0d-493e-ac51-3aa1df922986/datepart-to-get-2-digit-number-for-the-month?forum=sqlintegrationservices

But not sure what happens when month is not single digit.

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2014-02-07 : 07:14:43

Try this:

SELECT convert(varchar(4),datepart(yyyy, GETDATE())) + '_' + case LEN(convert(varchar(2),datepart(mm, GETDATE()))) when 1 then right('0'+ convert(varchar(2),datepart(mm, GETDATE())),2) 
else convert(varchar(2),datepart(mm, GETDATE()))  
end + '_' + case LEN(convert(varchar(2),datepart(dd, GETDATE()))) when 1 then right('0'+ convert(varchar(2),datepart(dd, GETDATE())),2) 
else convert(varchar(2),datepart(dd, GETDATE()))  
end

ranvir_2k
Posting Yak Master

180 Posts

Posted - 2014-02-07 : 07:17:43

Thanks, that worked, as did:

select convert(varchar(4),(datepart(yy,getdate())))+'_'+substring (convert(varchar(10),getdate(),112), 5, 2)+'_'+ substring (convert(varchar(10),getdate(),112), 7, 2)

mhorseman
Starting Member

44 Posts

Posted - 2014-02-07 : 08:30:30

How about

SELECT replace(CONVERT(VARCHAR(10), GETDATE(), 120),'-','_')

?

Mark

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2014-02-07 : 08:59:55

quote:
Originally posted by mhorseman

How about

SELECT replace(CONVERT(VARCHAR(10), GETDATE(), 120),'-','_')

?

Mark

That cuts the code down a good bit Mark

G

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources