Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Dates calculation

Posted on 2007-12-04
9
Medium Priority
?
303 Views
Last Modified: 2010-03-20
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
0
Comment
Question by:Oneill0003
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 20406223
Hi,

--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
0
 
LVL 35

Expert Comment

by:David Todd
ID: 20406250
Hi,

Previous Saturday is
--Beginning of this week is
select dateadd( week, datediff( week, -2, getdate() ) - 1, -2 )

HTH
  David
0
 

Author Comment

by:Oneill0003
ID: 20406289
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
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 35

Expert Comment

by:David Todd
ID: 20406612
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      
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 20406831
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

Open in new window

0
 

Author Comment

by:Oneill0003
ID: 20413025
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.
0
 

Author Comment

by:Oneill0003
ID: 20530822
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!
0
 
LVL 27

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 20530890
it's been a while since I wrote the code but basically I used the SQL date functions to calcuate and compare the given date with a date from previus week.

- datename(weekday,@dta) function returns the name of the day (Monday, Tuesday) for the date given if the first parameter is weekday. What the case does is turning every weekday name into a number from 1 to 7 so you can calculate the difference.

- month(date) returns the month number like 1 for January, 2 for Feb, etc.

I compare the month of the given date with the month for the monday of the previous week. If they are the same return one thing, according to your requirements, if they differ return something else.

When you have an expression like date1 - n by default SQL server returns a date2 which is from n days before day1. So, for instance, if is the given date is on Tuesday the date for the Monday previous week will be date - 8, thats why:

month(@dta - CASE WHEN 'Moday' then 7 WHEN 'Tuesdat' then 8...) gives you the date for the previous moday from where I compare the month with the month of the given date. This is how I check if the last week had days in different months.

I follow the same logic to cover all the cases and to pull out the dates you needed.

I hope I was clear enough.
0
 

Author Comment

by:Oneill0003
ID: 20534086
Clear enough!

Thanks!
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question