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.

  • Merging every two rows of data in a column in SQL Server
  • Why does this T-SQL OUTPUT INTO with FOREIGN KEY hack work?
  • Fill factor SQL Server
  • SQL Server Query for selecting rows with times based on a specified user-inputted timespan
  • Stored procedure, left join gives me duplicate rows, but I want top(1) row for each record with same ID
  • SQL Server - Creating an Indexed View
  • 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”

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.