Compare the Dates - 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 2005 Forums

Transact-SQL (2005)

Compare the Dates

Author

Topic

dpad
Starting Member

6 Posts

Posted - 2010-03-30 : 10:15:54

Hi,
I have a requirement wherein I need to convert the Date Column stored as Char(8) to DateTime and compare that column with the the system generated date witht he start ofther month.

I have tweaked the system date to generate the first day of the current month successfully :
dateadd(mm,datediff(mm,0,getdate()),0)

Also converting the char(8) column to DATETIME is working fine.

The problem arises when I try comparing to filter data based on the current month only. The select is listed below.

select
CONVERT(DATETIME, CONVERT(CHAR(8),COLUMN_A ))
from
TABLE_A
where
CONVERT(DATETIME, CONVERT(CHAR(8),COLUMN_A )) >= dateadd(mm,datediff(mm,0,getdate()),0) and
COLUMN_A!= '00000000'

The Error :
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Please let me know on this. Need to make it dynamic.

Thank You,
D

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-30 : 10:21:56

1) Why are you storing dates as CHARS?

2) Can you specify the dateformat in the field? 'yyyymmdd' ?

3) sample data if possible.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

dpad
Starting Member

6 Posts

Posted - 2010-03-30 : 10:28:35

The dates are stored in the format
20100120
20100120
19940114
19961216
19970825

This data comes from the source system and I have no control of changing the format. I require this for information reports. Also when there is no valid date the value stored in the database is : 00000000
I have filtered this in the where clause as well.

IF I do not compare and just convert the date ...it works fine .
like :

select
CONVERT(DATETIME, CONVERT(CHAR(8),COLUMN_A ))
from
TABLE_A
where
COLUMN_A!= '00000000'....

But I need to compare it with the getdate() for the current montth dates.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-30 : 10:31:07

the problem is that you can't short circuit the order of operation of the WHERE clause.

Dates that are in the format '00000000' are being cast to DATETIMES and will fail

You can probably use a derived table. Something like


SELECT
	[convDate]
FROM
	(
	select 
		CONVERT(DATETIME, CONVERT(CHAR(8),COLUMN_A )) AS [convDate]
	from
		TABLE_A
	where
		COLUMN_A <> '00000000'
	)
	filt
WHERE
	filt.[convDate] >= dateadd(mm,datediff(mm,0,getdate()),0)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-30 : 10:33:30

You should look into changing the column. Even if you have no control over the data coming in. If it is coming in as 'yyyymmdd' then you can convert it to DATETIME directly. You could use a similar approach as the derived table about to turn '00000000' into NULL before storing it.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-30 : 10:43:06

From your select statement, it appears you have values in COLUMN_A that are not in a valid date format. eg. '00000000'. You'll need to remove those first. However, Charlie is right. If you're storing date data, store it in a DATETIME field.


select
CONVERT(DATETIME, CONVERT(CHAR(8),COLUMN_A ))
from (
	SELECT COLUMN_A
	FROM TABLE_A
	WHERE ISDATE(COLUMN_A) = 1) z
where
CONVERT(DATETIME, CONVERT(CHAR(8),COLUMN_A )) >= dateadd(mm,datediff(mm,0,getdate()),0)

There are 10 types of people in the world, those that understand binary, and those that don't.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-30 : 10:48:26

quote:
Originally posted by Transact Charlie
COLUMN_A <> '00000000'

Better off using WHERE ISDATE(COLUMN_A) = 1, as it will ensure all non date formats are filtered out. eg, there may be a source record with '0000000' or just '0', that works fine in the source system. It might not be there today, but if it's added later, it'll break this query.

There are 10 types of people in the world, those that understand binary, and those that don't.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-30 : 11:13:28

Yes. If you want to use isdate(), use len() function too for reliability
http://beyondrelational.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx

Madhivanan

Failing to plan is Planning to fail

dpad
Starting Member

6 Posts

Posted - 2010-03-30 : 11:18:04

I did apply the logic as below :
select
ConvDate
from (
SELECT
CONVERT(DATETIME, CONVERT(CHAR(8),Column_a )) AS ConvDate
FROM CLAIM_PMT_RSV
WHERE column_a <> '00000000') filt ,table_a
where
filt.ConvDate >= dateadd(mm,datediff(mm,0,getdate()),0)

It filters out the dates. But the afer problem is instead of me seeing 7 rows it shows be 24000 rows with all duplicated values. I think it is doing the cross product. Please let me know on this.

Thanks,D

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-30 : 11:18:54

quote:
Originally posted by DBA in the making

quote:
Originally posted by Transact Charlie
COLUMN_A <> '00000000'

Better off using WHERE ISDATE(COLUMN_A) = 1, as it will ensure all non date formats are filtered out. eg, there may be a source record with '0000000' or just '0', that works fine in the source system. It might not be there today, but if it's added later, it'll break this query.

There are 10 types of people in the world, those that understand binary, and those that don't.

Fair comment.

The only thing I'd come back with is that using ISDATE can't use any index on the column. You'd have to do a table scan to eliminate the rouge dates.

Of course, due to the poor storage choice, performance is going to be terrible anyway so it's probably a moot point.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

dpad
Starting Member

6 Posts

Posted - 2010-03-30 : 11:20:28

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-30 : 11:40:33

Get rid of this bit.


,table_a

That's causing an join from every record returned by the subquery with every record in table_a

And while your at it, ask around who named that table, and give them a foot in the bum.

There are 10 types of people in the world, those that understand binary, and those that don't.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-30 : 11:43:31

quote:
Originally posted by madhivanan

Yes. If you want to use isdate(), use len() function too for reliability
http://beyondrelational.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx

Thanx for that madhivanan. I'll remember that.

quote:
Originally posted by Transact Charlie
Of course, due to the poor storage choice, performance is going to be terrible anyway so it's probably a moot point.

Due to the poor storage choice, I'd gone with the assumption that there was no index on that column. :)

There are 10 types of people in the world, those that understand binary, and those that don't.

dpad
Starting Member

6 Posts

Posted - 2010-03-30 : 11:52:29

I figured that out. But when I remove that table_a............it still throws me :
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Evden when the sub query is being used it throws the same error

select
ConvDate
from (
SELECT
CONVERT(DATETIME, CONVERT(CHAR(8),Column_a )) AS ConvDate
FROM CLAIM_PMT_RSV
WHERE column_a <> '00000000') filt
where
filt.ConvDate >= dateadd(mm,datediff(mm,0,getdate()),0)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-30 : 11:55:15

Try isdate then. Maybe there is data in there that isn't like you think it is. try this?


SELECT
	[convDate]
FROM
	(
	select 
		CONVERT(DATETIME, COLUMN_A) AS [convDate]
	from
		TABLE_A
	where
		ISDATE(COLUMN_A) = 1
	)
	filt
WHERE
	filt.[convDate] >= dateadd(mm,datediff(mm,0,getdate()),0)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-30 : 11:57:00

And also you'll probably want to find the rows that are causing the problems


SELECT *
FROM
    TABLE_A
WHERE
    ISDATE(COLUMN_A) = 0

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

dpad
Starting Member

6 Posts

Posted - 2010-03-30 : 12:12:29

Even the isdate function is yeilding the same error.
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

SELECT
[convDate]
FROM
(
select
CONVERT(DATETIME,COLUMN_A ) AS [convDate]
from
TABLE_A
where
ISDATE(COLUMN_A) = 1
)
filt
WHERE
filt.[convDate] >= dateadd(mm,datediff(mm,0,getdate()),0)

BUT THE INTERESTING THING IS WHEN I RUN THE BELOW QUERY HARD CODING THE VALUES IT DOES GIVE ME CORRECT RESULTS:

SELECT
CONVERT(DATETIME,COLUMN_A ) AS [convDate]
from
TABLE_A
WHERE
COLUMN_A <> '00000000' AND
COLUMN_A > '20100301'

ALSO WHEN I RUN THE BELOW CODE TO FIND THE ERROR CAUSING ROWS :
SELECT COLUMN_A
FROM
TABLE_A
WHERE
ISDATE(COLUMN_A) = 0

IT OLNY SHOWS ME ROWS WITH 00000000

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-30 : 12:20:16

try adding the command

SET DATEFORMAT ymd to the top...


SET DATEFORMAT ymd

SELECT
	[convDate]
FROM
	(
	select 
		CONVERT(DATETIME, CONVERT(CHAR(8),COLUMN_A )) AS [convDate]
	from
		TABLE_A
	where
		ISDATE(COLUMN_A) = 1
	)
	filt
WHERE
	filt.[convDate] >= dateadd(mm,datediff(mm,0,getdate()),0)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-30 : 12:30:22

This thread is why you shouldn't store Dates as Strings People!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-30 : 12:40:44

quote:
Originally posted by Transact Charlie

This thread is why you shouldn't store Dates as Strings People!

WORD!

There are 10 types of people in the world, those that understand binary, and those that don't.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-31 : 01:30:36

quote:
Originally posted by Transact Charlie

This thread is why you shouldn't store Dates as Strings People!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Yes. Thats why I always starts with
1 Use proper DATETIME datatype to store dates
.
.
.

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources