convert int to datetime - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

convert int to datetime

Author

Topic

mafeltz
Starting Member

7 Posts

Posted - 2009-07-15 : 10:13:58

i have a field called 'extract_date' in a table that is an int datatype and the data is formatted YYYYMMDD. I am trying to write a select statement to pull the last 500 days of entries from this table base on the date in the extract_date field. since I can't use the sql dateadd function on an int as my query:
'extract_date >= DATEADD([day], DATEDIFF([day], 0, GETDATE()) - 500, 0)'
returns:
'arithmatic overflow error converting expression to data type datetime'

So i tried to convert my int datatype to varchar and that works successfully using:
(CONVERT(varchar(8), extract_date)

now i am looking to convert to varchar and select the last 500 days entried in one shot. I have tried:
(CONVERT(varchar(8), extract_date) >= DATEADD([day], DATEDIFF([day], 0, GETDATE()) - 500, 0))

and receive the following error:
the conversion of a char data type to datetime has resulted in an out of range datetime value

I'm fairly new to writing datatype conversion queries so any help would be appreciated.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-15 : 10:34:05

declare @a int
set @a = 20090715

select Convert(smalldatetime, convert(char(8), @a))

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-07-15 : 11:29:22

Since your column is in integer form, you should convert the cutoff date to the same integer format in the where clause.

The likely reason that you are getting a convesion error is that you have an invalid date in some rows. That is one reason why you should always store dates in datatime columns.


select
	*
from
	MyTable
where
	extract_date >=
	-- Date 500 days before today in int form YYYYMMDD
	convert(int,convert(varchar(8),dateadd(dd,-500,getdate()),112))

CODO ERGO SUM

mafeltz
Starting Member

7 Posts

Posted - 2009-07-15 : 15:55:07

Thanks Michael. your statement is exactly what i was trying to accomplish.

Russell, thanks as well. I see now how i can use that as a variable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources