Optimal way to convert to date
I have legacy system where all date fields are maintained in YMD format. Example:
20101123 this is date: 11/23/2010
I’m looking for most optimal way to convert from number to date field.
Here is what I came up with:
declare @ymd int set @ymd = 20101122 select @ymd, convert(datetime, cast(@ymd as varchar(100)), 112)
This is pretty good solution but I’m wandering if someone has better way doing it
4 Solutions collect form web for “Optimal way to convert to date”
CONVERT(DATETIME, CONVERT(NVARCHAR, YYYYMMDD))
SELECT CONVERT(DATETIME, CONVERT(NVARCHAR, 20100401))
What you have is a pretty good soltuion.
Why are you looking for a better way?
I use exactly that, it has been working fine for me
As it is stored as an integer then you could potential extract the year, month and day by dividing by 100, 1000.
DECLARE @Date INT SET @Date = 20100401 DECLARE @Year INT DECLARE @Month INT DECLARE @Day INT SET @Year = @Date / 10000 SET @Month = (@Date - (@Year * 10000)) / 100 SET @Day = @Date - (@Year * 10000) - (@Month * 100) SELECT @Date, DATEADD(MONTH,((@Year-1900)*12)+@Month-1,@Day-1)
However, I have no idea if that is faster than the string comparison you already have. I think your solution is far cleaner and easier to read and would stick with that.