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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Calculating Between Dates
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mlawton
Starting Member

35 Posts

Posted - 10/31/2013 :  12:38:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 10/31/2013 :  12:42:23  Show Profile  Reply with Quote
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 - 10/31/2013 :  13:32:24  Show Profile  Reply with Quote
Thanks!!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 10/31/2013 :  14:03:08  Show Profile  Reply with Quote
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 - 10/31/2013 :  14:27:51  Show Profile  Reply with Quote
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

USA
550 Posts

Posted - 10/31/2013 :  16:31:38  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 10/31/2013 :  16:38:36  Show Profile  Reply with Quote
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 - 11/01/2013 :  11:15:46  Show Profile  Reply with Quote
Thanks!! I used the calculation for the Month End.
Go to Top of Page

mlawton
Starting Member

35 Posts

Posted - 03/13/2014 :  10:57:56  Show Profile  Reply with Quote
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 - 03/13/2014 :  11:25:21  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000