Convert Date to numeric

How can I convert a date like 'Jan 1 2014 12:00AM' to 20140101?

when the month is less then 10 I need to put a zero.

I tryed with datepart but had problem adding '0' before it.

thank's.

2 Answers

SELECT YEAR(DateField) * 10000 + MONTH(DateField) * 100 + DAY(DateField) 

To convert it to an integer. Or

SELECT convert(char(8), DateField, 112) 

To convert it to a yyyymmdd string.

If your date is currently a string, you need to convert it to a real datetime value first:

SELECT CONVERT(datetime, 'Jan 1 2014 12:00AM') 

(mon dd yyyy hh:miAM (or PM) is the SQL Server default format for datetime conversions, so there is no need for a format specifier here.)

If it's already a datetime, you can simply use formatting option 112, which represents YYYYMMDD:

SELECT CONVERT(nvarchar(8), myDateTimeField, 112) 

(Nesting the two statements as required or converting the string result to a numeric data type is left as an exercise to the reader.)