Between Dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Between Dates

Author

Topic

Looper
Yak Posting Veteran

68 Posts

Posted - 2008-06-02 : 12:20:59

I have a select query that selects records that have changed between a range of dates i.e. last date the process was run and the current new run date.

The problem is I think this may be missing records. The termination date is formatted 02/06/2008 in the database whereas @f_last_date_run and @f_new_date_run dates are set with date and time values. Therefore if the last run date is 02/06/2008 20:00:00 then the termination date 02/06/2008 will be missing wont it? How can I ensure I get all between these dates each week and don't take the same record the next week as well.


Select Name, Address etc...
From Table
WHERE TerminationDate between @f_last_date_run and @f_new_date_run

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 12:24:55

Didnt understand how. Since your db is not storing time even when you pass 02/06/2008 20:00:00 you will have to retrive the records which have TerminationDate till 02/06/2008 which will be done by above query.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-02 : 12:25:33

Select Name, Address etc...
From Table
WHERE TerminationDate >= @f_last_date_run
and TerminationDate < dateadd(day, 1, @f_new_date_run)

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 12:35:22

quote:
Originally posted by Peso

Select Name, Address etc...
From Table
WHERE TerminationDate >= @f_last_date_run
and TerminationDate < dateadd(day, 1, @f_new_date_run)

E 12°55'05.25"
N 56°04'39.16"

Wont this bring next day records also? The db has dates without time part and OP is passing time part through parameter. So adding 1 to end date means we will be getting next days records as well.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-02 : 14:48:36

Not if the @f_new_date_run has 00:00 as time. We don't know.
OP doesn't supply enough information.

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 15:03:45

quote:
Originally posted by Peso

Not if the @f_new_date_run has 00:00 as time. We don't know.
OP doesn't supply enough information.

E 12°55'05.25"
N 56°04'39.16"

But OP says it has time part

Therefore if the last run date is 02/06/2008 20:00:00 then the termination date 02/06/2008 will be missing wont it? H

Looper
Yak Posting Veteran

68 Posts

Posted - 2008-06-02 : 15:37:09

Hey

I think what is happening is the termination date is being represented as 02/06/2008 00:00:00. Therefore when the last date is 02/06/2008 20:00 then it is not picking it out.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-03 : 00:51:53

quote:
Originally posted by Looper

Hey

I think what is happening is the termination date is being represented as 02/06/2008 00:00:00. Therefore when the last date is 02/06/2008 20:00 then it is not picking it out.

yeah thats the problem i guess. SO we need to strip off time part from dateparams like

DATEADD(dd,DATEDIFF(dd,0,@f_last_date_run),0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources