14

I am using MS SQL Server 2008, and in a table I have column idate as date but in the integer format. But in the query I want that in the date format. Is it possible to convert integer date into proper datetime format?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Vikrant More
  • 5,182
  • 23
  • 58
  • 90

4 Answers4

20

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

qxotk
  • 2,384
  • 5
  • 24
  • 39
davey
  • 1,544
  • 4
  • 26
  • 41
  • 1
    just reread your question, if want as a date time then just do select cast(40835 as datetime) – davey Oct 20 '11 at 01:18
  • 1
    I had to subtract 2 days off my integer value (from excel) to get the correct date, e.g. select cast(40835-2 as datetime) – cgage1 Apr 29 '19 at 19:00
4

You have to first convert it into datetime, then to date.

Try this, it might be helpful:

Select Convert(DATETIME, LEFT(20130101, 8))

then convert to date.

Kijewski
  • 25,517
  • 12
  • 101
  • 143
Nikhil Gutha
  • 41
  • 1
1

You most likely want to examine the documentation for T-SQL's CAST and CONVERT functions, located in the documentation here: http://msdn.microsoft.com/en-US/library/ms187928(v=SQL.90).aspx

You will then use one of those functions in your T-SQL query to convert the [idate] column from the database into the datetime format of your liking in the output.

qxotk
  • 2,384
  • 5
  • 24
  • 39
0

Reading through this helps solve a similar problem. The data is in decimal datatype - [DOB] [decimal](8, 0) NOT NULL - eg - 19700109. I want to get at the month. The solution is to combine SUBSTRING with CONVERT to VARCHAR.

    SELECT [NUM]
       ,SUBSTRING(CONVERT(VARCHAR, DOB),5,2) AS mob
    FROM [Dbname].[dbo].[Tablename]
Anthony Simon Mielniczuk
  • 333
  • 3
  • 10