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

 

 

Answer 1

"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

Answer 2

hi ..

No its declared @date as  'date'..

input :20010101(yr/m/dd)

required output : 2001-01-01

 

Answer 3

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

 

Answer 4

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

Answer 5

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)