A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.
I’m writing a simple select statement from a table where today (aka the day on which the query is run) must be between the start and end dates.
It looks like this:
Select * from MyTable where getdate() between dtStart and dtEnd.
Simple enough... yet: the returned records are correct, except for one: it returns a record where the dtStart is 1901-01-01 (it doesn't do for the other records where the dtStart is 1901-01-01 ; only one)
What is the problem?
By the way, I’m working with MS Sql 2005. (Oh, and I don’t want the Sql statement to be too complex, I’m later going to have to “convert” it to be able to use it in MS Dynamix Ax - which has it's own integrated sql syntax).
Is it a data issue, should I use a datediff statement instead?
The problem is that GETDATE() returns a date and time.
If dtStart and dtEnd contain dates only (no time), you can do it as follows:
WHERE GETDATE() >= dtStart AND GETDATE() < dtEnd + 1
If dtStart and dtEnd contain date/time values, then I'd do it something like the following (the dateadd(...) function strips the time from GETDATE):
DECLARE @Today DATETIME
SET @Today = dateadd(dd, datediff(dd,''19000101'',@GETDATE()), ''19000101'')
SELECT ... WHERE dtStart >= @Today and dtEnd < @Today + 1
Monday, April 21, 2008
It just dawned on me that what was returned was in fact correct...
Today is between 1901-01-01 and 9999-12-31.
I think I better go home and lie down - before I hurt myself.
This topic is archived. No further replies will be accepted.Other recent topics
Powered by FogBugz