| 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. |
|
|
|
|
Problem with comparing datetime values.