[END_OF_MONTH_DATE]	=
		dateadd(day,-1,dateadd(month,datediff(month,0,a.[DATE])+1,0)),

[END_OF_MONTH_DATE]	=
		dateadd(month, datediff(month, -1, a.[DATE]), -1),

	[END_OF_YEAR_DATE]	=
		dateadd(day,-1,dateadd(year,datediff(year,0,a.[DATE])+1,0)) ,

	[END_OF_QUARTER_DATE]	=
		dateadd(day,-1,dateadd(quarter,datediff(quarter,0,a.[DATE])+1,0)) ,

	[END_OF_YEAR_DATE]	=
		dateadd(year, datediff(year, -1, a.[DATE]), -1) ,

	[END_OF_QUARTER_DATE]	=
		dateadd(quarter, datediff(quarter, -1, a.[DATE]), -1) ,

quote:

---

Originally posted by Peso

Great function! I will use this when creating a date table in a Data Mart for a customer after vacation.

However,

[END_OF_MONTH_DATE]	=
		dateadd(day,-1,dateadd(month,datediff(month,0,a.[DATE])+1,0)),

uses 4 arithmetic operations, as far as I can interpret the formula. 2 dateadds, 1 datediff and 1 plus sign. If the formula where changed to

[END_OF_MONTH_DATE]	=
		dateadd(month, datediff(month, -1, a.[DATE]), -1),

how would that impact speed, since there then only would be 2 arithmetic operations, 1 dateadd and 1 datediff?


Peter Larsson
Helsingborg, Sweden

---

quote:

---

Originally posted by khtan

Actually MVJ uses the more simply method in this thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=68642.

---

quote:

---

set dateformat dmy
select convert(datetime, '9997-12-31')

---

quote:

---

select convert(datetime, '99971231')

---

quote:

---

Originally posted by madhivanan

So YYYYMMDD is pure Universal?

Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by Michael Valentine Jones

Yes, I am aware that there are places where I could simplify the code in this function, but I haven't done it because the function was intended mostly to be used to load a permanent table, and it didn't seem to be worth the effort of changing and especially testing. Overall, I doubt that the changes suggested would make much difference in overall performance.

I am also aware that it is intended to work only with US English formats, but I really don't feel like putting in the effort to "internationalize" it. Perhaps I should have added some notes to explain this, especially the date format setting that should be used.

CODO ERGO SUM

---

quote:

---

Originally posted by khtan

It seems YYYY/MM/DD or YYYY-MM-DD will still give error to dateformat ydm

---

quote:

---

Originally posted by Michael Valentine Jones

Yes, I am aware that there are places where I could simplify the code in this function, but I haven't done it because the function was intended mostly to be used to load a permanent table, and it didn't seem to be worth the effort of changing and especially testing. Overall, I doubt that the changes suggested would make much difference in overall performance.

I am also aware that it is intended to work only with US English formats, but I really don't feel like putting in the effort to "internationalize" it. Perhaps I should have added some notes to explain this, especially the date format setting that should be used.






CODO ERGO SUM

---

SELECT @MyDateVar = DATE FROM vwNow

CREATE VIEW dbo.vwNow
AS

SELECT	CAST(CURRENT_TIMESTAMP + 53689.49999996 AS INT) AS [DATE_ID],
	CAST(CURRENT_TIMESTAMP - 0.50000004 AS INT) AS [SQL_DATE_ID],
	CAST(CURRENT_TIMESTAMP - 2.50000004 AS INT) AS [MSOFFICE_DATE_ID],
	DATEADD(DAY, CAST(CURRENT_TIMESTAMP - .50000004 AS INT), 0) AS [DATE],
	DATEADD(DAY, CAST(CURRENT_TIMESTAMP - .50000004 AS INT), 1) AS [NEXT_DAY_DATE],
	DATEPART(YEAR, CURRENT_TIMESTAMP) AS [YEAR],
	DATEPART(QUARTER, CURRENT_TIMESTAMP) AS [QUARTER],
	DATEPART(MONTH, CURRENT_TIMESTAMP) AS [MONTH],
	DATEPART(DAYOFYEAR, CURRENT_TIMESTAMP) AS [DAY_OF_YEAR],
	DATEPART(DAY, CURRENT_TIMESTAMP) AS [DAY_OF_MONTH],
	CASE DATENAME(WEEKDAY, CURRENT_TIMESTAMP)
		WHEN 'Sunday' THEN 1
		WHEN 'Monday' THEN 2
		WHEN 'Tuesday' THEN 3
		WHEN 'Wednesday' THEN 4
		WHEN 'Thursday' THEN 5
		WHEN 'Friday' THEN 6
		WHEN 'Saturday' THEN 7
	END AS [DAY_OF_WEEK],
	DATEADD(YEAR, DATEDIFF(YEAR, 0, CURRENT_TIMESTAMP), 0) AS [START_OF_YEAR_DATE],
	DATEADD(YEAR, DATEDIFF(YEAR, -1, CURRENT_TIMESTAMP), -1) AS [END_OF_YEAR_DATE],
	DATEADD(QUARTER, datediff(quarter, 0, CURRENT_TIMESTAMP), 0) AS [START_OF_QUARTER_DATE],
	DATEADD(QUARTER, DATEDIFF(QUARTER, -1, CURRENT_TIMESTAMP), -1) AS [END_OF_QUARTER_DATE],
	DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) AS [START_OF_MONTH_DATE],
	DATEADD(MONTH, DATEDIFF(MONTH, -1, CURRENT_TIMESTAMP), -1) AS [END_OF_MONTH_DATE],
	DATEADD(DAY,	CASE DATENAME(WEEKDAY, CURRENT_TIMESTAMP)
				WHEN 'Sunday' THEN -6
				WHEN 'Monday' THEN 0
				WHEN 'Tuesday' THEN -1
				WHEN 'Wednesday' THEN -2
				WHEN 'Thursday' THEN -3
				WHEN 'Friday' THEN -4
				WHEN 'Saturday' THEN -5
			END, CAST(CURRENT_TIMESTAMP - 0.50000004 AS INT)) AS [START_OF_WEEK_STARTING_MON_DATE],
	DATEADD(DAY,	CASE DATENAME(WEEKDAY, CURRENT_TIMESTAMP)
				WHEN 'Sunday' THEN 0
				WHEN 'Monday' THEN 6
				WHEN 'Tuesday' THEN 5
				WHEN 'Wednesday' THEN 4
				WHEN 'Thursday' THEN 3
				WHEN 'Friday' THEN 2
				WHEN 'Saturday' THEN 1
			END, CAST(CURRENT_TIMESTAMP - 0.50000004 AS INT)) AS [END_OF_WEEK_STARTING_MON_DATE],
	DATEDIFF(QUARTER, '17530101', CURRENT_TIMESTAMP) AS [QUARTER_SEQ_NO],
	DATEDIFF(MONTH, '17530101', CURRENT_TIMESTAMP) AS [MONTH_SEQ_NO],
	DATEDIFF(DAY, '17530101', CURRENT_TIMESTAMP) / 7 AS [WEEK_STARTING_MON_SEQ_NO],
	CAST(CASE DATENAME(WEEKDAY, DATEADD(YEAR, DATEDIFF(YEAR, 0, CURRENT_TIMESTAMP), 0))
		WHEN 'Sunday' THEN -6.50000004
		WHEN 'Monday' THEN 0.50000004
		WHEN 'Tuesday' THEN -1.50000004
		WHEN 'Wednesday' THEN -2.50000004
		WHEN 'Thursday' THEN -3.50000004
		WHEN 'Friday' THEN -4.50000004
		WHEN 'Saturday' THEN -5.50000004
	END + DATEPART(DAYOFYEAR, CURRENT_TIMESTAMP) / 7 AS INT) + 1 AS [ISO_WEEK_NO],
	CASE DATENAME(WEEKDAY, CURRENT_TIMESTAMP)
		WHEN 'Sunday' THEN 7
		WHEN 'Monday' THEN 1
		WHEN 'Tuesday' THEN 2
		WHEN 'Wednesday' THEN 3
		WHEN 'Thursday' THEN 4
		WHEN 'Friday' THEN 5
		WHEN 'Saturday' THEN 6
	END AS [ISO_DAY_OF_WEEK]