select current date -90 days 2

[cjbrown815]

SQL server 2005//management studio

I have a field called sample_status (varchar) and a field called reg_on that is (datetime)


Please fix this statement

select * from sample where sample_status='N' and reg_on > currentdate -90


thank you

-CJ

 

[ca8msm]

Fix it? Why, what is wrong with it?


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[r937]

reg_on > getdate()-90

unfortunately sql server 2005 doesn't support the standard sql function CURRENT_DATE


r937.com | rudy.ca

 

[ca8msm]

What is currentdate? Did you mean GetDate() or @currentdate?


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[cjbrown815]

Thanks Rudy, this worked


SELECT * FROM SAMPLE WHERE SAMPLE_STATUS='N' AND REG_ON > ( GETDATE( ) - 90 )

-CJ

 

[gmmastros]

CJ,

I encourage you to double-check your results.

The (potential) problem here is that GetDate() returns a Date AND a time. This may or may not be right depending on your data structure. For example, today is 6 AUG 2007. GetDate() will return '2007-08-06 10:51:20.000 AM'.

Select GetDate() - 90

Returns...

2007-05-08 10:51:54.043

So, your query would NOT return records where the date is 2007-05-08 if the time component is before 10:51 AM. Again, this may be what you want, but if it's not, then you have more work to do. So... please double check to make sure this is what you want.

-George

"the screen with the little boxes in the window." - Moron

 

[cjbrown815]

Thanks George,
This is working well enough, hwoever I would like to return just the records beyond the net 90. I would have to convert the field to just date first? I don't know how to do that.

-CJ

 

[gmmastros]

Well... I'm glad you asked.

There are various ways to remove the time component from a datetime variable/column. Open a Query Analyzer window and run the following command(s)

Select GetDate()

Select DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)

Notice that the first one will show you a date AND a time. The second will also show you a date and time, but the second queries time will be 0 (midnight).

So, you're thinking... cool, how do I use that? Something like this should work.

SELECT * 
FROM   SAMPLE 
WHERE  SAMPLE_STATUS='N' 
       AND REG_ON >=  DateAdd(Day, DateDiff(Day, 0, GetDate()-90), 0)

-George

"the screen with the little boxes in the window." - Moron

 

[cjbrown815]

Thanks a tonne

SQL2005// CRXIr2// XP Pro
-CJ

PROGRESS LIES NOT IN ENHANCING WHAT IS, BUT IN ADVANCING TOWARD WHAT WILL BE.
-KHALIL GIBRAN 1883-1931

 

[ESquared]

An improvement for you, George...

SELECT * 
FROM   SAMPLE 
WHERE  SAMPLE_STATUS='N' 
       AND REG_ON >=  DateDiff(Day, 90, GetDate())

Notice the missing DateAdd. I used base 90 instead of 0, too.

Unless you are leaving it in on purpose to avoid confusing people...

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[gmmastros]

ESquared,

DateDiff returns an integer, so when I run...

select DateDiff(Day, 90, GetDate())

I get... 39208

To compare this with a datetime column, there would have to be an implicit data type conversion. By using DateAdd, we are in essence doing explicit data type conversion (by adding that number of days to the 'zero' date).

I'm not a big fan of implicit data type conversions, but if there is a valid reason for using your syntax over mine, I'd be glad to hear about it.

-George

"the screen with the little boxes in the window." - Moron

 

[ESquared]

Convert(datetime, DateDiff(Day, 90, GetDate()))

IS faster, in my testing, than

DateAdd(Day, DateDiff(Day, 90, GetDate()), 0)

And it's also clearer (to me) what one is trying to do. As far as I remember, the implicit conversion performed about the same as the explicit convert, but both were faster than the dateadd method.

See the thread you missed, DateTime coolness - something new, surprisingly.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]