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
 General SQL Server Forums
 Script Library
 Date Table Function F_TABLE_DATE
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 5

Kristen
Test

United Kingdom
22431 Posts

Posted - 02/23/2006 :  10:23:53  Show Profile  Reply with Quote
"But SQL Server 2000 let this passed"

I know !!
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/23/2006 :  21:22:21  Show Profile  Reply with Quote
I posted a change in the script to correct the order by. I usually don't code like the original statement, and I was a little surprised to see that it worked, after you pointed it out.

I don't have a SQL 2005 server to test it with, so if anyone has a chance, please give it a try in 2005.


CODO ERGO SUM
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 02/25/2006 :  05:45:46  Show Profile  Reply with Quote
Tested fine on SQL Server 2005.

----------------------------------
'KH'

It is inevitable
Go to Top of Page

klaus2000
Starting Member

3 Posts

Posted - 02/25/2006 :  08:30:26  Show Profile  Reply with Quote
thx a lot, perfect script!!
dramatically supports my work on BI stuff!




Go to Top of Page

Freddie
Starting Member

Canada
29 Posts

Posted - 03/29/2006 :  14:53:31  Show Profile  Reply with Quote
Thanks Michael...Your function works...FANTASTIC!!

cb
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 07/05/2006 :  03:05:02  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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?

Same thing would apply to
	[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)) ,
Rewritten, they could look like
	[END_OF_YEAR_DATE]	=
		dateadd(year, datediff(year, -1, a.[DATE]), -1) ,

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

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 07/05/2006 03:13:09
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 07/05/2006 :  03:10:12  Show Profile  Reply with Quote
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



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


KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 07/05/2006 :  03:15:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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.
Ah! I see that now. I still wonder if there would be any significant speed difference, though.

Keep up the good work Michael, and keep the function updated with new formulas.


Peter Larsson
Helsingborg, Sweden

EDIT: TYPO

Edited by - SwePeso on 07/05/2006 03:15:57
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 07/05/2006 :  08:49:35  Show Profile  Reply with Quote
With reference of this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=68670

It seems that with dateformat set to dmy, F_TABLE_DATE will encounter error
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

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

with dateformat set as dmy, it will result it above error. However, with dateformat ymd or mdy, it is fine.

quote:
select convert(datetime, '99971231')

With format YYYYMMDD, it will works with dmy, mdy, ymd and even ydm.




KH

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 07/05/2006 :  09:09:45  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
So YYYYMMDD is pure Universal?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 07/05/2006 :  09:17:58  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

So YYYYMMDD is pure Universal?

Madhivanan

Failing to plan is Planning to fail


I guess so. It seems YYYY/MM/DD or YYYY-MM-DD will still give error to dateformat ydm


KH

Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 07/05/2006 :  09:29:54  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 07/05/2006 :  09:48:51  Show Profile  Reply with Quote
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



No problem. Your contribution is greatly appreciated.

Developer using it should not have any difficulty to localize it to their date format or maybe even universalize it.


KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 07/05/2006 :  09:52:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by khtan

It seems YYYY/MM/DD or YYYY-MM-DD will still give error to dateformat ydm
When date or month is greater than 12, yes.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 07/07/2006 :  16:31:46  Show Profile  Reply with Quote
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



I modified the function to support systems running a non-US English DATEFORMAT. Also added a checksum test to the script to confirm that results are the same for different date formats. This change does not convert the various output formats to local formats; it only makes the function produce the same output no matter what the DATEFORMAT is.

I did not make any of the suggested changes to simplify various function calls. It's just too much work for now.

I wanted to make the changes for now to fix problems that would prevent it from executing on servers with non-US settings. Since this is an international site, and a lot of people have been using this function to answer questions, I thought it would be a help.



CODO ERGO SUM

Edited by - Michael Valentine Jones on 07/10/2006 11:30:59
Go to Top of Page

egghi
Starting Member

17 Posts

Posted - 11/03/2006 :  16:46:58  Show Profile  Reply with Quote
Hi Michael,

One of the experts in the forum point me to this posting but I still have no clue how and where (in Access vba? Is there anyway to do it in SQL?) to apply your function to my problem. I will appreciate it greatly if you may help me out!

I have a TimeOff table which contains 5 fields: timeoffID, employeename, startdate, enddate, and timofftype. I need to create a report which shows how many people take sick day (one of the timeofftypes) on each week day (i.e. Monday, Tuesday, Wednesday, and etc.) The problem is that I don't know how to convert the date range (from startdate to enddate) into indvidual week day for each timeoff record.

For example, in the SQL table:

TimeoffID employeename startdate enddate timeofftype
1 Andy 11/02/2006 11/03/2006 Sick Day
2 Bill 11/03/2006 11/03/2006 Sick Day

The report needs to show:

Mon Tue Wed Thu Fri Sat Sun
0 0 0 1 2 0 0


Thank you!
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/03/2006 :  17:03:26  Show Profile  Reply with Quote
This forum is not for posting questions.

You should post your questions on your original topic.




CODO ERGO SUM
Go to Top of Page

egghi
Starting Member

17 Posts

Posted - 11/03/2006 :  17:11:05  Show Profile  Reply with Quote
Sorry:(
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 03/02/2007 :  10:05:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Since user defined functions in SQL Server 2000 do not accept GETDATE in code, you can use this to emulate the behaviour.
I have used MVJ's excellent function as a base to do a VIEW.
Then all you have to do in your function
SELECT @MyDateVar = DATE FROM vwNow

Far from all columns used in MVJ's function are in the view below. Many of them are different formats for same value.

Here is the view
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]

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 03/02/2007 10:11:32
Go to Top of Page

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 05/21/2007 :  15:57:10  Show Profile  Reply with Quote
quick question, just tried to build this function in my sql 2000 database, and keep getting the following:

Server: Msg 137, Level 15, State 2, Procedure F_TABLE_DATE, Line 472
Must declare the variable '@start_date'.
Server: Msg 137, Level 15, State 1, Procedure F_TABLE_DATE, Line 473
Must declare the variable '@end_date'.


the database is configuration for a case sensitve collation
Go to Top of Page
Page: of 5 Previous Topic Topic Next Topic  
Previous Page | Next Page
 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.52 seconds. Powered By: Snitz Forums 2000