The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

MS SQL

Hi.
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?
Sprite
Monday, April 21, 2008
 
 
What's dtEnd for that row?
Larry Lard
Monday, April 21, 2008
 
 
9999-12-31 00:00:00.000
Sprite
Monday, April 21, 2008
 
 
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
Joe
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.

(*sigh*)

I think I better go home and lie down - before I hurt myself.
Sprite
Monday, April 21, 2008
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz