Calculating 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 2008 Forums

Transact-SQL (2008)

Calculating Between Dates

Author

Topic

mlawton
Starting Member

35 Posts

Posted - 2013-10-31 : 12:38:37

I have the following query:
Declare @Today datetime
Declare @MonthEnd datetime

Select date, acctnum, ordernum, scheduledate
from orderstable
where date = @Today
and scheduledate between @Today and @MonthEnd

The monthend date is always the 21st of the month and when @today is the 21st of the month, I will only get the 21st of the month data.
For example: Today is 10/31/2013 and Monthend will be 11/21/2013;
Today is 11/21/2013 and Monthend will be 11/21/2013;
Today is 11/30/2013 and Monthend will be 12/21/2013;

Can someone please show me how to rewrite this query?

Thanks!!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-10-31 : 12:42:23

Assuming the Dates are just the date and there is no time component:

Select date, acctnum, ordernum, scheduledate
from orderstable
where date = @Today
and scheduledate >= @Today 
and scheduledate < DATEADD(DAY, 1, @MonthEnd)

mlawton
Starting Member

35 Posts

Posted - 2013-10-31 : 13:32:24

Thanks!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-31 : 14:03:08

will scheduledate have time part?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mlawton
Starting Member

35 Posts

Posted - 2013-10-31 : 14:27:51

No. It will be as follows:
SELECT @Today = convert(varchar(11), (getdate()), 101).

I don't know what @monthend should be.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-10-31 : 16:31:38

You should make the data type of the variable @Today exactly the same as the data type of "date" column in "ordersTable". I'll assume for now it's datetime. Note that I use "<" not "<=" on the ending scheduledate comparison.


Declare @Today datetime
Declare @MonthEnd datetime

SET @Today = GETDATE()

Select date, acctnum, ordernum, scheduledate
from dbo.orderstable
where date = @Today
and scheduledate >= @Today and 
    scheduledate < CONVERT(char(6), case when day(@Today) <= 21 THEN @Today ELSE DATEADD(MONTH, 1, @Today) END, 112) + '22'

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-10-31 : 16:38:36

Here is another way to calculate the Month End using date math:

SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', SYSDATETIME()) + CASE WHEN DAY(SYSDATETIME()) > 21 THEN 1 ELSE 0 END, '19000122')

mlawton
Starting Member

35 Posts

Posted - 2013-11-01 : 11:15:46

Thanks!! I used the calculation for the Month End.

mlawton
Starting Member

35 Posts

Posted - 2014-03-13 : 10:57:56

I have this same issue again. I used the following:

DECLARE @MonthEnd datetime
SELECT @MonthEnd = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', SYSDATETIME()) + CASE WHEN DAY(SYSDATETIME()) > 21 THEN 1 ELSE 0 END, '19000121')
SELECT @MonthEnd

and this gives me:
2014-03-21 00:00:00.000

How can I re-write this so that my @MonthEnd is
2014-04-21 00:00:00.000?

Thanks.

mlawton
Starting Member

35 Posts

Posted - 2014-03-13 : 11:25:21

Nevermind. I figured it out.

SELECT @MonthEnd = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', SYSDATETIME()) + CASE WHEN DAY(SYSDATETIME()) > 21 THEN 1 ELSE 0 END, '19000221')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources