T-SQL datetime conversion

I’m working with a 3rd party database, which (for whatever reason) internal stores a date in the format yyyymm eg 201212 for Dec 2012 as an INT.

Is there a simple way that I may convert this to a SQL Server DateTime type?

  • Regex in SQL Server to check whitespaces
  • SQL Server 2000 - ALTER TABLE + INSERT INTO = Errors?
  • SQL Server: Select records which have any HTML entities within a VARCHAR(MAX) column
  • Can a table have multiple Primary keys?
  • Computing difference in rows for all except consecutive days?
  • Do you generate your data dictionary?
  • I’ve tried manually converting the above to a char seq of length 6, concatenating this with ’01’ to get the start of a given month like so:

    (CONVERT(char(6), Period) + '01')`

    then wrapping this in a datetime conversion:

    CONVERT(datetime, (CONVERT(char(6), Period) + '01'))

    but this seems to throw;

    Conversion failed when converting date and/or time from character string

    Can anyone shed any light on what I’m doing wrong?

    Thanks! 🙂

    7 Solutions collect form web for “T-SQL datetime conversion”

    You need to use the correct parameter. Here’s the msdn chart http://msdn.microsoft.com/en-us/library/ms187928.aspx

    In your case, i believe you need to append the ’01’ and use this

    declare @val VARCHAR(8)
    SET @val = '201212'
    set @val = @val + '01'
    print CONVERT(DATETIME, @val, 12)

    12 is the ISO format and will take either yymmdd or yyyymmdd

    This is one way to do it:

    Declare @val as int = 20121231;
    select DATEADD (YEAR,@val/10000-1900,DATEADD(MONTH,(@val%10000)/100-1,DATEADD(DAY,@val%100-1,0 )))

    This is another:

    Declare @val as int = 20121231;
        Cast(Cast(@val as char(8)) as date)

    The first is good because you won’t have any regional setting dependencies. The second is good because it’s brief.

    Convert the integer to a yyyy-mm-dd style string:

    select  convert(datetime, 
                left(cast(dt as char(6)),4) + '-' +
                right(cast(dt as char(6)),2) + '-01',
                121) -- 121 = ODBC = yyyy-mm-dd hh:mi:ss.mmm (24h)
    from    YourTable

    Live example at SQL Fiddle.

    you can use as below:

    Select CAST( CAST(datepart(year,getdate()) AS VARCHAR(4))+ CAST (datepart(MONTH,getdate()) as VARCHAR(2)) AS INT) as YYYYMM

    This should do the stuff :

    DECLARE @Period INTEGER = 201212 
    SELECT  CAST( CAST( @Period * 100 + 1 AS CHAR(8) ) AS DATETIME )

    You can do it with some simple maths, avoiding strings as much as possible:

    declare @Start int
    set @Start = 201212
    select DATEADD(month,((@Start/100)-1970)*12 + (@Start%100-1),'19700101')

    That being said, I’d have expected what you originally posted to work, since YYYYMMDD is an unambiguous format.

    If you’re selecting these values from a table that may contain a range of values, only some of which are these values, and you’re relying on the WHERE clause to filter out invalid ones before the conversion occurs, you’re out of luck – SQL Server has a nasty habit of moving operations around, and can apply filters and conversions in any order it likes.

    If this is the case, move the filtering step (to exclude invalid Period values) into a separate query that populates a table variable or temp table, then perform the conversion on those values in a separate query.

    I’ve found the following to work quite well.
    Assuming your INT for the date is in YYYYMM format.
    First multiply by 100, then add 1 (this results in an INT with a DD of 01) so you now have YYYYMMDD instead of YYYYMM.
    Then cast to CHAR(8) before casting to DATETIME.
    Hope it works for you.

    DECLARE @SourceDT   INT = 201212
    SELECT CAST( CAST((@SourceDT * 100) + 1 AS CHAR(8)) AS DATETIME)
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.