Problem with comparing datetime values.

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

Transact-SQL (2000)

Problem with comparing datetime values.

Author

Topic

lathangi.ch
Starting Member

6 Posts

Posted - 2009-11-25 : 23:43:29

Hi,
I am a newbie to this forum but not to sql server

.This is the first time i am having a problem in comparing two datetime values.I am trying to develop an ageing report for my data.Here is the sample data..


Name   Amt     <30   30-60    60-90   90-365  1yr-2yr    >2yr
---------------------------------------------------------------
John   $25.50  10.0    -      15.50     -       -         -
Smith  $17.25    -    10.00     -      7.25     -         -
Bill   $10.00  10.0    -        -       -       -         -

case 1(Under30):
-------------
For this my query would be like
select name, amt
....
..
from tablename
where txndate < getdate() - 30

case 2(above 30 and below 60):
------------------------------
select name, amt
....
..
from tablename
where convert(varchar,txndate,106) between convert(varchar,getdate()-30,106) and convert(varchar,getdate()- 59,106)

The above query worked for 30-60 and 60-90. But i don't know what went wrong with the code it started giving null data for 90-365 and for the other two cases.

Any advice is much appreciated.

Thanks in advance,
Lathangi.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-26 : 01:31:24

It means no data for those dates

Madhivanan

Failing to plan is Planning to fail

lathangi.ch
Starting Member

6 Posts

Posted - 2009-11-26 : 15:03:27

quote:
Originally posted by madhivanan

It means no data for those dates

But I have data for those dates

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-27 : 01:44:07

Post some sample data with expected output

Madhivanan

Failing to plan is Planning to fail

lathangi.ch
Starting Member

6 Posts

Posted - 2009-11-30 : 13:32:14

quote:
Originally posted by madhivanan

Post some sample data with expected output

Madhivanan

Failing to plan is Planning to fail

Output to my query is already posted in my question.

Thanks,
Lathangi

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-30 : 14:02:12

First there is no need to convert to varchar when comparing dates.
Second if you are using between then the lower date has to be first...I mean
where date_column between getdate()-60 and getdate()-30

Third you should better use dateadd(day,-30,getdate()) for example.

Hope that helps

No, you're never too old to Yak'n'Roll if you're too young to die.

lathangi.ch
Starting Member

6 Posts

Posted - 2009-11-30 : 15:08:55

quote:
Originally posted by webfred

First there is no need to convert to varchar when comparing dates.
Second if you are using between then the lower date has to be first...I mean
where date_column between getdate()-60 and getdate()-30

Third you should better use dateadd(day,-30,getdate()) for example.

Thanks for your kind suggestions

But still i have problems with the time part of the datetime.

For example, consider the below case :


Input data

Name   Charges  Outstanding      txndate
        Amount   Amount  

smith	6.00	1.25	        2009-11-21 15:04:09.000
smith	6.00	1.25	        2009-11-21 15:04:08.000
smith	6.00	3.25	        2009-09-02 12:13:39.000
smith	6.00	0.25	        2009-08-24 10:50:58.000

Output
Name   Amt     <30   30-59    60-89   90-365  366-730    >2yr
---------------------------------------------------------------
smith  6.00    2.50  NULL     NULL    0.25     NULL      NULL

The amount 3.25 doesn't show up under 60-89 column.

FYI : I am using

case when TxnDate between getdate()-89 and getdate()-60 
then SUM(OutstandingAmount)
END AS "Between60and89"

to retrieve column 3 data.

Thanks,
Lathangi

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-11-30 : 15:29:03

That is because the time when you execute also matters when you are using getdate(). The time is greater than 2009-09-02 12:13:39.000 which is why it's not showing up...you need to strip the time portion and check like..

where txnDate between dateadd(d, datediff(d, 0, getdate()), 0)-89 and dateadd(d, datediff(d, 0, getdate()), 0)-60

you are better off using dateadd like webfred suggested as below...

where txnDate between dateadd(d,-89,dateadd(d, datediff(d, 0, getdate()), 0)) and dateadd(d,-60,dateadd(d, datediff(d, 0, getdate()), 0))

lathangi.ch
Starting Member

6 Posts

Posted - 2009-11-30 : 15:51:02

Thanks to madhivanan, webfred, vijayisonly for resolving my query.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-11-30 : 16:00:31

ur welcome

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-30 : 16:49:04

Now you can see that posting sample data is the key to get a solution

And I mean not the first post in this thread...

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources