none
convert int to date in sql server 2008

    Question

  • hi...

    input : 20010101(yr/m/dd)

    output: 2001-01-01

    I need int to date (not as datetime)

    what i tried is

    select CONVERT(date, CAST('20010101' AS CHAR(12)), 112)

    it works fine

    but when i try it like this

    select @date = CONVERT(date, CAST('20010101' AS CHAR(12)), 112)

    error:Operand type clash: date is incompatible with int

    any Suggestion,

    thank you

     

     

    Saturday, June 26, 2010 9:36 AM

Answers

  • The code your suggest runs without any problems. See below

     

    Declare @date date
    select @date = CONVERT(date, CAST('20010101' AS CHAR(12)), 112)
    select @date
    
               
    ---------- 
    2001-01-01
    
    (1 row(s) affected)
    
    So either your local variable is not declared as date, or you replaced the literal with a local variable that is not a string.
    -- 
    Gert-Jan

     

    Saturday, June 26, 2010 10:22 AM
  • That is all fine but this algorithm is both faster and obviously can never have any language or regional settings dependencies.

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

    Although if you like brevity this is not much slower and has no settings dependencies either.

    cast(cast(@val as char(8)) as date)
    Friday, August 19, 2011 7:06 PM

All replies

  • "error:Operand type clash: date is incompatible with int"

    I guess you must have declare the variable @date as int

    what do you want actually ?


    KH Tan
    • Proposed as answer by KiranKumar.Y Saturday, June 26, 2010 7:09 PM
    Saturday, June 26, 2010 9:49 AM
  • hi ..

    No its declared @date as  'date'..

    input :20010101(yr/m/dd)

    required output : 2001-01-01

     

    Saturday, June 26, 2010 10:09 AM
  • The code your suggest runs without any problems. See below

     

    Declare @date date
    select @date = CONVERT(date, CAST('20010101' AS CHAR(12)), 112)
    select @date
    
               
    ---------- 
    2001-01-01
    
    (1 row(s) affected)
    
    So either your local variable is not declared as date, or you replaced the literal with a local variable that is not a string.
    -- 
    Gert-Jan

     

    Saturday, June 26, 2010 10:22 AM
  • Hi there,

      Tan & Gert-Jan are correct, see the difference, the first one will give you the same error msg what you are getting currently and the second one gives you the exact result.

    Declare @date int 
    select @date = CONVERT(date, CAST('20010101' AS CHAR(12)), 112) -- Operand type clash: date is incompatible with int
    select @date
    go

    Declare @date date
    select @date = CONVERT(date, CAST('20010101' AS CHAR(12)), 112)
    select @date
    go

    hope its clear.


    Kiran
    Saturday, June 26, 2010 7:16 PM
  • That is all fine but this algorithm is both faster and obviously can never have any language or regional settings dependencies.

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

    Although if you like brevity this is not much slower and has no settings dependencies either.

    cast(cast(@val as char(8)) as date)
    Friday, August 19, 2011 7:06 PM