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)
Go to Top of Page

mlawton
Starting Member

35 Posts

Posted - 2013-10-31 : 13:32:24
Thanks!!!
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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'
Go to Top of Page

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')
Go to Top of Page

mlawton
Starting Member

35 Posts

Posted - 2013-11-01 : 11:15:46
Thanks!! I used the calculation for the Month End.
Go to Top of Page

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.
Go to Top of Page

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')
Go to Top of Page
   

- Advertisement -