compare date not working.. - 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 2008 Forums

Transact-SQL (2008)

compare date not working..

Author

Topic

asifbhura
Posting Yak Master

165 Posts

Posted - 2011-05-19 : 09:39:17

Hello

I have table, which contains one column name bydate as nvarchar(10)

but it contains date like 19/05/2011,22/05/2011

now i want to compare this column to date after 90 days

I tried like

SELECT * FROM SMSByServices WHERE CONVERT(DATETIME,ByDate,103)= DATEADD(day,90,CONVERT(VARCHAR(10), GETDATE(), 103))

but it is not working

it gives error like

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-19 : 09:44:27

Change it to

SELECT * FROM SMSByServices WHERE CONVERT(DATETIME,ByDate,103)=DATEADD(DAY,90,GETDATE());

asifbhura
Posting Yak Master

165 Posts

Posted - 2011-05-19 : 10:08:58

hello sir,

thank you

but not working

I am having data in table as like 19-08-2011 etc....

I tried your query

but gives nothing

i tried like this to check first,

SELECT DATEADD(DAY,92,GETDATE());

this gives exact date and time which is in table (19-08-2011)

then i tried like

SELECT * FROM SMSByServices WHERE CONVERT(DATETIME,ByDate,103)=DATEADD(DAY,92,GETDATE());

but it gives no error but no record as well, while there is data with same date in table

may be

SELECT DATEADD(DAY,92,GETDATE());

this return with time, while in table only date no time

can you help me

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-19 : 10:18:08

Sorry about that, I should have seen the EQUALS sign in your query.


SELECT * FROM SMSByServices WHERE CONVERT(DATETIME,ByDate,103)=DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),90);

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-19 : 10:20:44

SELECT * FROM SMSByServices WHERE DATEDIFF(D,bydate,DATEADD(DAY,92,GETDATE())) =0

--------------------------
http://connectsql.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-19 : 10:26:21

quote:
Originally posted by lionofdezert

SELECT * FROM SMSByServices WHERE DATEDIFF(D,bydate,DATEADD(DAY,92,GETDATE())) =0

--------------------------
http://connectsql.blogspot.com/

Much better!!

asifbhura
Posting Yak Master

165 Posts

Posted - 2011-05-20 : 09:11:55

hi suniabeck

thank you it worked

SELECT * FROM SMSByServices WHERE CONVERT(DATETIME,ByDate,103)=DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),90);

but can you explain bold part

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-20 : 09:36:16

DATEDIFF(DAY,0,GETDATE()) counts the number of days between the date represented by 0 (whatever that is), and today's date. (It just so happens that date 0 is Jan 1,1900 in the internal representation of datetime data type.) You can see that if you just runthis:


select DATEDIFF(DAY,0,GETDATE())

The outer dateadd function adds 90 days to that. The result of the dateadd function is a date, so you get the date that is 90 days from now.

lionofdezert's query does a similar thing - you will get the same results, but it is more efficient because it has fewer function calls.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-05-20 : 12:40:02

If you can, you should change your columns to use proper data types. Storing dates as strings is going to cause you nothing but trouble.

However, if you are already stuck with a bad design, you might be able to gain some performance be using a sargable predicate. For example:

SELECT * 
FROM SMSByServices 
WHERE ByDate = CONVERT(VARCHAR(10), DATEADD(DAY, 90, CURRENT_TIMESTAMP), 105)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources