Date Criteria - 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

General SQL Server Forums

New to SQL Server Programming

Date Criteria

Author

Topic

fengfeng
Yak Posting Veteran

64 Posts

Posted - 2010-03-01 : 09:08:42

I have the following sql:

Select * from history where maildate = 20100302
--and processdate>= '20100301'

Instead of changing the sql daily to the current date, is there a way t have the maildate = today's date + 1 day, so tomorrow's date. Then is there a way to say if the maildate = saturday or sunday, then dont use tomorrow's date but use the saturday or sunday date, and have the processdate= the following monday's date if the maildate is on the saturday or sunday? If it is on a weekday then disregard the processdate.

Any help is appreciated

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-01 : 09:11:53

Select * from history
where
maildate >= dateadd(day,datediff(day,0,getdate())+1,0) and
maildate < dateadd(day,datediff(day,0,getdate())+2,0)

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-01 : 09:14:15

1. you should specify the date in string


Select * from history where maildate = '20100302'

2. to add working day use fn_next_business_day() from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130526

KH
[spoiler]Time is always against us[/spoiler]

fengfeng
Yak Posting Veteran

64 Posts

Posted - 2010-03-01 : 09:33:28

quote:
Originally posted by madhivanan

Select * from history
where
maildate >= dateadd(day,datediff(day,0,getdate())+1,0) and
maildate < dateadd(day,datediff(day,0,getdate())+2,0)

Madhivanan

Failing to plan is Planning to fail

This is helpful, but how do I get it to something like this:

If Maildate <> weekend then maildate = dateadd(day,datediff(day,0,getdate())+1,0) and processdate = null
else maildate = weekend then maildate = dateadd(day,datediff(day,0,getdate()),0) and process date = the following monday

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-01 : 09:41:00

(
(
maildate = dateadd(day,datediff(day,0,getdate())+1,0) and processdate = null
and datename(weekday,maildate) not in ('saturday','sunday')
)
or
(
maildate = dateadd(day,datediff(day,0,getdate())+1,0) and
processdate=dateadd(day,case when datename(weekday,maildate) = 'saturday' then 2 else 1 end,maildate)
and datename(weekday,maildate) in ('saturday','sunday')

)
)

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-01 : 10:49:31

quote:
Originally posted by madhivanan

(
(
maildate = dateadd(day,datediff(day,0,getdate())+1,0) and processdate = null
and datename(weekday,maildate) not in ('saturday','sunday')
)
or
(
maildate = dateadd(day,datediff(day,0,getdate())+1,0) and
processdate=dateadd(day,case when datename(weekday,maildate) = 'saturday' then 2 else 1 end,maildate)
and datename(weekday,maildate) in ('saturday','sunday')

)
)

Madhivanan

Failing to plan is Planning to fail

it should be

and processdate is null

unless your ANSI NULL settings are off

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-02 : 01:31:01

Thanks. I think it is copy/paste thing