SQL Server Forums
Profile | Register | 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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mlawton
Starting Member

30 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

30 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
52325 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

30 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
Constraint Violating Yak Guru

USA
437 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

30 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

30 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

30 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  
 New 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.36 seconds. Powered By: Snitz Forums 2000