CODEWITHSUNDEEP
sql-server-2008
Jordan1200
Jordan1200

Reputation: 488

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.

Upvotes: 0

Views: 13240

Answers (2)

Heinzi
Heinzi

Reputation: 172380

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

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453618

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.

Upvotes: 1

Related Questions