Date convertion

How can I convert this date format to YYYYMMDD?

2013-04-02 00:00:00.000

to

YYYYMMDD
20130402
VBdotnet2005Asked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
> For some reason this works in my SSMS
You are converting it to 'Date', thats why it doesn't strip of the '-'s,

SELECT CONVERT(varchar, cast('2013-04-02 00:00:00.000' as datetime), 112)
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
For some reason this works in my SSMS
Declare @dt datetime = '2013-04-02 00:00:00.000'
SELECT REPLACE(CONVERT(date, @dt, 112), '-', '')

Open in new window

and this doesn't
SELECT REPLACE(CONVERT(date, '2013-04-02 00:00:00.000', 112), '-', '')

Open in new window

0
 
ValentinoVBI ConsultantCommented:
In case you expect your output to be string, I'd suggest this:

Declare @dt datetime = '2013-04-02 00:00:00.000'
SELECT CONVERT(char(8), @dt, 112)

Or perhaps you're expecting a number? Then:

Declare @dt datetime = '2013-04-02 00:00:00.000'
SELECT CONVERT(int, CONVERT(char(8), @dt, 112))
0
 
Alpesh PatelConnect With a Mentor Assistant ConsultantCommented:
if SQL server 2012 then


Select Format(GETDATE(),'yyyyMMdd')
0
 
sachitjainConnect With a Mentor Commented:
SQL Server any version, this would surely work

declare @dt datetime
set @dt = getdate()
select convert(varchar(4), year(@dt)) + right('0' + convert(varchar(2), month(@dt)), 2) + right('0' + convert(varchar(2), day(@dt)), 2)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.