Converting a datetime to a numeric representation

Question

I want to convert a datetime field into a numeric representation in form of YYYYMMDD. So, my logic here is (from 2011-01-01 12:00:00.000 to 20110101) :

convert(int, replace(cast(getdate() as date), '-', ''))

According to MSDN ( http://msdn.microsoft.com/en-us/library/bb630352.aspx ), the string representation is [always?] "YYYY-MM-DD", so I simply convert that string to an INT after removing dashes from the string.

Will this always works? Will I encounter some problems with that? Is there a better way to achieve this?

Thanks

Solution

That approach can work, not sure what would happen with localization settings. If you use the built in datetime conversion function options (http://msdn.microsoft.com/en-us/library/ms187928.aspx) you can avoid using the replace and not worry about locales.

Example:

select CAST(convert(varchar,getdate(),112) as int)

Comments

[Mike Walsh]

(Format 112 is the ISO date/time format - I prefer using it whenever I work with dates, works regardless of where I or my system is - yyyymmdd... ) Just replace getdate() with your date field. 112 will also remove the time.

[Dominic Goulet]

Really like it. Works pretty well and is a shorter form. Thanks !

[Moe Sisko]

@Mike Walsh - I'm nitpicking, but maybe slightly better to use : "varchar(8)" instead of "varchar" (with implied default of length 30) in the convert function.

[Mike Walsh]

Perhaps nitpicking, perhaps not. The output will be an 8 and there won't be any issue of truncation or data issues. I don't believe there would be an implicit conversion issue between varchar(n) and varchar(m) but it never hurts to be explicit here :-)