OnError_Fix
asked on
Why does this datetime between not work?
Hi folks,
What am I missing here? My sp is supposed to return only the records from the table where today's date (represented by GetDate()) is between the [PhaseStartDate] and [PhaseEndDate].
Here's the SQL I'm using:
SELECT PhaseID,PhaseStartDate,Pha
WHERE GetDate() BETWEEN PhaseStartDate AND PhaseEndsDate
Looks simple enough, but it doesn't appear to be doing any 'BETWEEN' stuff at all.
Dates in my table are in the format 103 (dd/mm/yyyy) but I can easily convert these to UTC if it's wiser?
Sample table data:
PhaseID,PhaseStartDate,Pha
1,#29/04/2005 10:00:00#,#10/05/2005 23:00:00#
2,#20/04/2005 10:00:00#,#10/05/2005 23:00:00#
*** PLEASE NOTE: THE TIME IS IMPORTANT! ***
When I run this query, it returns both phases, even though today is 30/04/2005.
What gives?!
Thanks,
OnError_Fix
What am I missing here? My sp is supposed to return only the records from the table where today's date (represented by GetDate()) is between the [PhaseStartDate] and [PhaseEndDate].
Here's the SQL I'm using:
SELECT PhaseID,PhaseStartDate,Pha
WHERE GetDate() BETWEEN PhaseStartDate AND PhaseEndsDate
Looks simple enough, but it doesn't appear to be doing any 'BETWEEN' stuff at all.
Dates in my table are in the format 103 (dd/mm/yyyy) but I can easily convert these to UTC if it's wiser?
Sample table data:
PhaseID,PhaseStartDate,Pha
1,#29/04/2005 10:00:00#,#10/05/2005 23:00:00#
2,#20/04/2005 10:00:00#,#10/05/2005 23:00:00#
*** PLEASE NOTE: THE TIME IS IMPORTANT! ***
When I run this query, it returns both phases, even though today is 30/04/2005.
What gives?!
Thanks,
OnError_Fix
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry --- they ARE in Date/Time datatype fields, only they are in the format of dd/mm/yy.
With regards to the fact that it is returning both phases, I now feel like the world's biggest idiot :) That's what happens when you've been playing with datetimes for the past 4 hours solid!
Sorry about that!
One minor question tho, even though when I use query analyser, it returns the date in the format dd/mm/yy hh:mm:ss, there is no hh:mm:ss when I view the table in Enterprise Manager. Why's that?
With regards to the fact that it is returning both phases, I now feel like the world's biggest idiot :) That's what happens when you've been playing with datetimes for the past 4 hours solid!
Sorry about that!
One minor question tho, even though when I use query analyser, it returns the date in the format dd/mm/yy hh:mm:ss, there is no hh:mm:ss when I view the table in Enterprise Manager. Why's that?
The grid rendering engine is different between QA and EM. Probably because at some time in the past one or both originated outside of Microsoft and were bought in. The grid in EM for example also shows nulls explicitly as <NULL> where QA simple shows a blank. If the date time has a non-zero time element the grid in EM does show it.
Anthony.
Anthony.
Anthony.