How to Convert Int to Date in SQL Server 2008

How to convert int to date in SQL Server 2008

You can't convert an integer value straight to a date but you can first it to a datetime then to a date type

select cast(40835 as datetime)

and then convert to a date (SQL 2008)

select cast(cast(40835 as datetime) as date)

cheers

Convert INT to DATETIME (SQL)

you need to convert to char first because converting to int adds those days to 1900-01-01

select CONVERT (datetime,convert(char(8),rnwl_efctv_dt ))

here are some examples

select CONVERT (datetime,5)

1900-01-06 00:00:00.000

select CONVERT (datetime,20100101)

blows up, because you can't add 20100101 days to 1900-01-01..you go above the limit

convert to char first

declare @i int
select @i = 20100101
select CONVERT (datetime,convert(char(8),@i))

Convert an Int to a date field

Simple cast as date could work

Select cast(cast(20161011 as varchar(8)) as date)

Returns

2016-10-11

If your data is suspect, you could also use Try_Convert()

Select Try_Convert(date,cast(2610 as varchar(8)))

Returns

NULL

convert int to date to add a dayn and convert to int back

Here is a nice exercise and I hope it works out for you...

declare @date date

declare @newDate date

set @date = convert(date, '20100101')

set @newdate = DateAdd(dd, 1, @Date)

select @date

select @newdate

select convert(int, convert(varchar, @newdate, 112))  -- this is your final conversion back to int

SQL Converting Int to Date or Char

Your problem is that you are comparing an INT column with STRING values, and expecting it to resolve as if comparing dates...

WHERE date_paid >= '09/01/2012'
  AND date_paid <= '09/30/2012' 

-- date_paid is an INT (according to your question)
-- '09/01/2012' is a STRING

Because of the differing datatypes there is an implicit CAST() in there. Effectively you're doing...

WHERE CAST(date_paid AS VARCHAR(10)) >= '09/01/2012'
  AND CAST(date_paid AS VARCHAR(10)) <= '09/30/2012' 

-- NOTE:  All of these values are now strings
--        They may LOOK like dates, but they're just strings

What you really need to do is explicitly manipulate the strings in to integers.

WHERE date_paid >= DATEDIFF(DAY, '01/01/1753', '09/01/2012') + 639906
  AND date_paid <= DATEDIFF(DAY, '01/01/1753', '09/30/2012') + 639906

This too involves implicit casts. DATEDIFF() only takes DATETIME datatypes so the strings are implicitly cast to DATETIMEs first.

EDIT:

Another option would be to CAST() the date_paid field into a DateTime, then base the WHERE caluse on that. The down side here is...

- The CAST() would have to be done on every row, then the WHERE clause applied

- This prevents any use of indexes and dramatically reduces performance

The answer above does all the manipulation on the constant values, so that the searched field can be processed in it's native state; thus allowing use of indexes.

---

Related Topics