datediff question

[sandeep0000]

i have a field dis_end_dt (disablity end dt)
i want to get the dates that will expire in 30 days or less , prior to the run date of my report

i done this before , just cant remember

sorry,

and thanks

 

[gmmastros]

GetDate() returns the current date and time.


Try using DateDiff...

[tt][blue]DateDiff(Day, dis_end_dt, GetDate())[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[sandeep0000]

how do i narrow it down to 30 days or less?

 

[sandeep0000]

im still getting dates over 30 days with

select *
from cis.dbo.INDV_DIS w
here indv_dis_typ_cd = 'te'
and DateDiff(Day, dis_end_dt, GetDate()) <= 30
order by 3

 

[gmmastros]

Try...

select * 
from cis.dbo.INDV_DIS w
here indv_dis_typ_cd = 'te'
and   DateDiff(Day, [!]GetDate(), [/!]dis_end_dt) <= 30
 order by 3

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

select *
from cis.dbo.INDV_DIS
where indv_dis_typ_cd = 'te' AND
      BETWEEN DateDiff(Day, dis_end_dt, GetDate()) 0 AND 30 
order by 3

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bborissov]

O!
You need records that will expire in NEXT 30 days?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[sandeep0000]

thanks for all your guys/girls help

this seemed to work after i got the tip on the coding from u all.

select * from cis.dbo.INDV_DIS where indv_dis_typ_cd = 'te'
and DateDiff(Day, GetDate(), dis_end_dt) >= 0 and
DateDiff(Day, GetDate(), dis_end_dt) <= 30
order by 3

i want to get people that are about to expire in 30 days or less,
the data seems right, does the code make sense???

 

[LNBruno]

Just a couple of things:

1) Do you really need ALL the columns from the table (SELECT *) ?

2) If the column order in the table changes, what impact will that have (ORDER BY 3 - instead of the actual column name)

< M!ke >
Acupuncture Development: a jab well done.

 

[bborissov]

Just theoretical (never did a proper tests) I think that:

select *
from cis.dbo.INDV_DIS
where indv_dis_typ_cd = 'te' AND
      BETWEEN DateDiff(Day, GetDate(), dis_end_dt) 0 AND 30
order by 3

will be a little bit faster because DateDiff is evaluated only once per record, but I am not sure at all

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[gmmastros]

When written like this, if there is an index on the disp_end_dt column, it will not be used. For better performance, you will want to use this instead.

[COLOR=blue]select[/color] * 
[COLOR=blue]from[/color]   cis.dbo.INDV_DIS 
[COLOR=blue]where[/color]  indv_dis_typ_cd = [COLOR=red]'te'[/color]
       and disp_end_dt >= [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Day[/color], [COLOR=#FF00FF]DateDiff[/color]([COLOR=#FF00FF]Day[/color], 0, [COLOR=#FF00FF]GetDate[/color]()), 0)
       And disp_end_dt < [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Day[/color], [COLOR=#FF00FF]DateDiff[/color]([COLOR=#FF00FF]Day[/color], 0, [COLOR=#FF00FF]GetDate[/color]()), 31)
[COLOR=blue]order[/color] [COLOR=blue]by[/color] 3

Notice that I am using 31. This is ok because we are also using a 'less than' operation on the end date.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[sandeep0000]

damn u guys are great, all this help , so quick

thanks all

IT WORKED