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 numbe开发者_如何学Gor 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

try this:

CONVERT(DATETIME, CONVERT(NVARCHAR, YYYYMMDD))

For example:

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

Results in:

2010-04-01 00:00:00.000

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.

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.