Get data that is older than 2 months

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 2005 Forums

Transact-SQL (2005)

Get data that is older than 2 months

Author

Topic

cendant
Starting Member

2 Posts

Posted - 2007-11-27 : 11:56:35

Hi, I would appreciate your help

I have a table with a column name named End_Of_Day_ID. It is nvarchar(30) and contains date stamp

Basically it looks like

123662007101916470500

The year is 2007, the month is 10 and the day is 19th, so I can extract that information with

SUBSTRING(End_Of_Day_ID, 6, 8)

Other information is not relevant

Now I want to copy only data which is older than 2 months, so I may want to use

< DATEADD(mm, -2, CURRENT_TIMESTAMP)

So I am trying to select the number of rows with

SELECT *
FROM [TOPDBArchive].[dbo].[EndOfDay]
WHERE End_Of_Day_ID IN (
SELECT End_Of_Day_ID
FROM [TOPDBArchive].[dbo].[EndOfDay]
WHERE CONVERT(datetime, SUBSTRING(End_Of_Day_ID, 6, 8)) < DATEADD(mm, -2, CURRENT_TIMESTAMP)
)

I get

Arithmetic overflow error converting expression to data type datetime.

What am I doing wrong?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-27 : 12:19:00

More than likely, you have values in End_Of_Day_ID that are not valid dates. That is one big disadvantage of storing dates in a column that is not a datetime datatype.

You will have to add a check for valid datetimes to your query, or correct the bad data.

CODO ERGO SUM

cr8nk
Yak Posting Veteran

77 Posts

Posted - 2007-11-27 : 12:23:02

Because it is an nvarchar field there is no guarantee that your data is valid datetime.

make sure this query can execute to determine the validity of your data.

select CONVERT(datetime, SUBSTRING(End_Of_Day_ID, 6, 8))
from [TOPDBArchive].[dbo].[EndOfDay]

cendant
Starting Member

2 Posts

Posted - 2007-11-27 : 12:32:18

Thank you for the answers.

Unfortunately, the above suggestion also bring me

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

We found a workaround

SELECT * FROM [TOPDBArchive].[dbo].[EndOfDay] where SUBSTRING(End_Of_Day_ID, 6, 14) <= '20070911235929'

where the first part of '20070911235929' is created from a date that is more than 2 months before

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-28 : 01:34:28

You need to make use of ISDATE() function


SELECT 
	*
FROM 
	[TOPDBArchive].[dbo].[EndOfDay]
WHERE 
	End_Of_Day_ID IN 
	(
		SELECT End_Of_Day_ID
		FROM [TOPDBArchive].[dbo].[EndOfDay]
		WHERE ISDATE(SUBSTRING(End_Of_Day_ID, 6, 8))=1 and 
		LEN(SUBSTRING(End_Of_Day_ID, 6, 8))=8  and 
		CONVERT(datetime, SUBSTRING(End_Of_Day_ID, 6, 8)) < DATEADD(mm, -2, CURRENT_TIMESTAMP)
	)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources