Oneill0003
asked on
Dates calculation
Hi experts:
Im having trouble trying to calculate MonthBegin and MonthToDate.
For this task Im always looking at the previous week, if the previous week has two months in it then:
MB = The 1st of the current month
MTD = Last Sunday of the current week
else
MB = The 1st of last month.
MTD = Last Sunday of the previous week
Example:
If I run it on 2007-11-07 then
MB = 2007-11-01 and MTD = 2007-11-04
If I run it on 2007-08-01 then
MB = 2007-07-01 and MTD = 2007-07-29
I keep trying and trying but not going anywhere, will really appreciate any help or tips!
Thanks
Isaac
Im having trouble trying to calculate MonthBegin and MonthToDate.
For this task Im always looking at the previous week, if the previous week has two months in it then:
MB = The 1st of the current month
MTD = Last Sunday of the current week
else
MB = The 1st of last month.
MTD = Last Sunday of the previous week
Example:
If I run it on 2007-11-07 then
MB = 2007-11-01 and MTD = 2007-11-04
If I run it on 2007-08-01 then
MB = 2007-07-01 and MTD = 2007-07-29
I keep trying and trying but not going anywhere, will really appreciate any help or tips!
Thanks
Isaac
Hi,
Previous Saturday is
--Beginning of this week is
select dateadd( week, datediff( week, -2, getdate() ) - 1, -2 )
HTH
David
Previous Saturday is
--Beginning of this week is
select dateadd( week, datediff( week, -2, getdate() ) - 1, -2 )
HTH
David
ASKER
dtodd:
Thanks for the responce, i know what the syntax to get MB and WB is, what i need is help in figuring out how to calculate those dates by looking at the previous week and checking to see if the previous week has 2 months in it.
Look at the example provided.
IF previous week has two months in it then
MB = The 1st of the current month
MTD = Last Sunday of the current week
else
MB = The 1st of last month.
MTD = Last Sunday of the previous week
end IF
If I run it on 2007-11-07 then
MB = 2007-11-01 and MTD = 2007-11-04
If I run it on 2007-08-01 then
MB = 2007-07-01 and MTD = 2007-07-29
Thanks for the responce, i know what the syntax to get MB and WB is, what i need is help in figuring out how to calculate those dates by looking at the previous week and checking to see if the previous week has 2 months in it.
Look at the example provided.
IF previous week has two months in it then
MB = The 1st of the current month
MTD = Last Sunday of the current week
else
MB = The 1st of last month.
MTD = Last Sunday of the previous week
end IF
If I run it on 2007-11-07 then
MB = 2007-11-01 and MTD = 2007-11-04
If I run it on 2007-08-01 then
MB = 2007-07-01 and MTD = 2007-07-29
Hi,
I see two ways to do this:
Use the previous saturday calculation above to see if it is the previous month,
2 use the day of month and day of week to caculate if there are enough days in this month.
set datefirst 6
select datepart( weekday, getdate() )
select datepart( day, getdate() ) - datepart( weekday, getdate() )
if datepart( day, getdate() ) - datepart( weekday, getdate() ) >= 7
print 'This Month'
else
print 'Last Month'
HTH
David
I see two ways to do this:
Use the previous saturday calculation above to see if it is the previous month,
2 use the day of month and day of week to caculate if there are enough days in this month.
set datefirst 6
select datepart( weekday, getdate() )
select datepart( day, getdate() ) - datepart( weekday, getdate() )
if datepart( day, getdate() ) - datepart( weekday, getdate() ) >= 7
print 'This Month'
else
print 'Last Month'
HTH
David
Try this:
DECLARE
@dta AS datetime
SELECT
@dta='2007-11-07'
SELECT
CASE
WHEN month(@dta-
CASE datename(weekday,@dta)
WHEN 'Monday' THEN 7
WHEN 'Tuesday' THEN 8
WHEN 'Wednesday' THEN 9
WHEN 'Thursday' THEN 10
WHEN 'Friday' THEN 11
WHEN 'Saturday' THEN 12
WHEN 'Sunday' THEN 13
END)
<>month(@dta-
CASE datename(weekday,@dta)
WHEN 'Monday' THEN 1
WHEN 'Tuesday' THEN 2
WHEN 'Wednesday' THEN 3
WHEN 'Thursday' THEN 4
WHEN 'Friday' THEN 5
WHEN 'Saturday' THEN 5
WHEN 'Sunday' THEN 7
END)
THEN cast(Year(@dta) AS varchar)+'-'+right('0'+cast(month(@dta) AS varchar),2)+'-01'
ELSE
cast(year(@dta-
CASE datename(weekday,@dta)
WHEN 'Monday' THEN 7
WHEN 'Tuesday' THEN 8
WHEN 'Wednesday' THEN 9
WHEN 'Thursday' THEN 10
WHEN 'Friday' THEN 11
WHEN 'Saturday' THEN 12
WHEN 'Sunday' THEN 13
END) AS varchar)+'-'+right('0'+cast(month(@dta-CASE datename(weekday,@dta)
WHEN 'Monday' THEN 7
WHEN 'Tuesday' THEN 8
WHEN 'Wednesday' THEN 9
WHEN 'Thursday' THEN 10
WHEN 'Friday' THEN 11
WHEN 'Saturday' THEN 12
WHEN 'Sunday' THEN 13
END) AS varchar),2)+'-01'
END AS MB,
convert(varchar(10),@dta-
CASE datename(weekday,@dta)
WHEN 'Monday' THEN 1
WHEN 'Tuesday' THEN 2
WHEN 'Wednesday' THEN 3
WHEN 'Thursday' THEN 4
WHEN 'Friday' THEN 5
WHEN 'Saturday' THEN 5
WHEN 'Sunday' THEN 7
END,121) AS MTD
ASKER
Zberteoc:
I have been trying your code and it seems to work just fine, can you give me some explanations of what you are doing, and will these code work for any future year?
I'm planning on making these into a function so i can just call it from my dts package.
Thanks a lot for the help.
I have been trying your code and it seems to work just fine, can you give me some explanations of what you are doing, and will these code work for any future year?
I'm planning on making these into a function so i can just call it from my dts package.
Thanks a lot for the help.
ASKER
Hey Zberteoc:
Your code work perfect do you think you can explain a little what the code is doing, i follow part of it but will like a better explanation.
Thanks a lot!
Your code work perfect do you think you can explain a little what the code is doing, i follow part of it but will like a better explanation.
Thanks a lot!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Clear enough!
Thanks!
Thanks!
--Beginning of this month is
select dateadd( month, datediff( month, 0, getdate() ), 0 )
-- Results 2007-12-01 00:00:00.000
--Beginning of this week is
select dateadd( week, datediff( week, 0, getdate() ), 0 )
-- Results 2007-12-03 00:00:00.000 which is a monday, so need to dial it back a couple of days.
hth
David