Author |
Topic |
mlawton
Starting Member
35 Posts |
Posted - 2013-10-31 : 12:38:37
|
I have the following query:Declare @Today datetimeDeclare @MonthEnd datetimeSelect date, acctnum, ordernum, scheduledatefrom orderstablewhere date = @Todayand scheduledate between @Today and @MonthEndThe 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, scheduledatefrom orderstablewhere date = @Todayand 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 MVPhttp://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 datetimeDeclare @MonthEnd datetimeSET @Today = GETDATE()Select date, acctnum, ordernum, scheduledatefrom dbo.orderstablewhere date = @Todayand 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 datetimeSELECT @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.000How can I re-write this so that my @MonthEnd is2014-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') |
|
|
|