Optimal way to convert to date

Question

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

Answer 1

What you have is a pretty good soltuion.

Why are you looking for a better way?

Answer 2

try this:

CONVERT(DATETIME, CONVERT(NVARCHAR, YYYYMMDD))

For example:

SELECT CONVERT(DATETIME, CONVERT(NVARCHAR, 20100401))

Results in:

2010-04-01 00:00:00.000

Answer 3

I use exactly that, it has been working fine for me

Answer 4

As it is stored as an integer then you could potential extract the year, month and day by dividing by 100, 1000.

e.g.

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.