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

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 08/20/2008 :  04:38:48  Show Profile  Visit spirit1's Homepage  Reply with Quote
no. the day of the week start is irrelevant. this is what i wanted

June 2008
WEEK_NUM   Mon Tue Wed Thu Fri Sat Sun
   1                                1 
   2        2   3   4   5   6   7   8 
   3        9   10  11  12  13  14  15
   4        16  17  18  19  20  21  22
   5        23  24  25  26  27  28  29
   6        30                         
   
August 2008
WEEK_NUM   Mon Tue Wed Thu Fri Sat Sun
   1                        1   2   3 
   2        4   5   6   7   8   9   10
   3        11  12  13  14  15  16  17
   4        18  19  20  21  22  23  24
   5        25  26  27  28  29  30  31
   6                                  

but as it seems i woun't be needing this after all.. thanx for suggestions though.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/20/2008 :  10:11:42  Show Profile  Reply with Quote
quote:
Originally posted by spirit1

no. the day of the week start is irrelevant. this is what i wanted

June 2008
WEEK_NUM   Mon Tue Wed Thu Fri Sat Sun
   1                                1 
   2        2   3   4   5   6   7   8 
   3        9   10  11  12  13  14  15
   4        16  17  18  19  20  21  22
   5        23  24  25  26  27  28  29
   6        30                         
   
August 2008
WEEK_NUM   Mon Tue Wed Thu Fri Sat Sun
   1                        1   2   3 
   2        4   5   6   7   8   9   10
   3        11  12  13  14  15  16  17
   4        18  19  20  21  22  23  24
   5        25  26  27  28  29  30  31
   6                                  

but as it seems i woun't be needing this after all.. thanx for suggestions though.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!



Well, the week does start on a certain day of the week. In your example above, the weeks start on Monday except for the first week of the month.

If you run the code I posted last, you will see that the results for WEEK_OF_MONTH are an exact match to your example.





CODO ERGO SUM
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 08/20/2008 :  10:54:19  Show Profile  Visit spirit1's Homepage  Reply with Quote
ok thanx... i'll play with it some more when i have time...

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!
Go to Top of Page

PaulSalmon
Starting Member

United Kingdom
2 Posts

Posted - 11/27/2008 :  07:58:07  Show Profile  Reply with Quote
Not as wizzy as Andy's calculation for holidays and public holidays but here's my addition for a BIT field to Michael's table to see if a date is part of the working week (Monday to Friday) :
[IS_WEEKDAY] = ((datediff(dd,'17530101',a.[DATE])%7))/5,

If your working week starts on Sunday then change the initial date to 17530107

Now I have a question - I wanted to add a field to track which Financial Year (FY) a date is in (I know this will vary from company to company so I wouldn't expect it to be standard part of the table). I had hoped to use a simple offset but I can't due to the start of FY occuring after the end of February, so I need to take leap years into account. I could do this via temporary table, like the ISO_WEEK table but that seems like overkill and I was hoping someone here could come up with a more elegant solution.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 11/27/2008 :  08:06:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by PaulSalmon

[IS_WEEKDAY] = ((datediff(dd,'17530101',a.[DATE])%7))/5

No. That will give you the weekends.

See this
SELECT	d.theDate,
	DATEDIFF(DAY, '17530101', d.theDate) % 7 / 5 AS IsWeekend,
	1 - DATEDIFF(DAY, '17530101', d.theDate) % 7 / 5 AS IsWeekday
FROM	(
		SELECT	'20081124' AS theDate UNION ALL
		SELECT	'20081125' UNION ALL
		SELECT	'20081126' UNION ALL
		SELECT	'20081127' UNION ALL
		SELECT	'20081128' UNION ALL
		SELECT	'20081129' UNION ALL
		SELECT	'20081130'
	) AS d


E 12°55'05.63"
N 56°04'39.26"

Edited by - SwePeso on 11/27/2008 08:07:33
Go to Top of Page

PaulSalmon
Starting Member

United Kingdom
2 Posts

Posted - 11/28/2008 :  08:22:10  Show Profile  Reply with Quote
Thanks Peso, you are correct. I was actually using the flag to deduct the number of weekend days from an interval so should have called the field IS_WEEKEND.

Can you help with my question about calculating the Financial Year?
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/28/2008 :  09:26:03  Show Profile  Reply with Quote
quote:
Originally posted by PaulSalmon

Thanks Peso, you are correct. I was actually using the flag to deduct the number of weekend days from an interval so should have called the field IS_WEEKEND.

Can you help with my question about calculating the Financial Year?



It would be better if you posted you question as a new topic, and provide an actual definition of your organization's Fiscal Year so that someone can help you without having to ask a lot of follow-up questions.



CODO ERGO SUM
Go to Top of Page

Weezy
Starting Member

USA
3 Posts

Posted - 01/21/2009 :  09:26:23  Show Profile  Reply with Quote
Michael:

Thanks for this function! I have a quesiton, well probalby a couple, and I am not asking you to do my work but I have a unique wrinkle. A customer in the entertainment business starts their week on a Friday. This ends up being all kinds of fun as you may imagine. I have written a couple Friday-finding functions and DOW functions and so on, but I think a lookup table for some data warehousing is going to be better in the long run, hence I found your function.

Your opinion: Should I rewrite your function to support their view of the universe? Should I just run your function and write 50-60 UPDATE statements to tweak the table along with manually editing those 5 days in 2010 that really belong to 2009?

I started to tweak things in the funciton and kept thinking of all of their blasted exceptions and thought I would get your opinion first.

Thanks again.
Weezy
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 01/21/2009 :  09:56:05  Show Profile  Reply with Quote
quote:
Originally posted by Weezy

Michael:

Thanks for this function! I have a quesiton, well probalby a couple, and I am not asking you to do my work but I have a unique wrinkle. A customer in the entertainment business starts their week on a Friday. This ends up being all kinds of fun as you may imagine. I have written a couple Friday-finding functions and DOW functions and so on, but I think a lookup table for some data warehousing is going to be better in the long run, hence I found your function.

Your opinion: Should I rewrite your function to support their view of the universe? Should I just run your function and write 50-60 UPDATE statements to tweak the table along with manually editing those 5 days in 2010 that really belong to 2009?

I started to tweak things in the funciton and kept thinking of all of their blasted exceptions and thought I would get your opinion first.

Thanks again.
Weezy



The function already has columns for support of weeks that start on Friday (or any day of the week):

[START_OF_WEEK_STARTING_FRI_DATE]	[datetime]	not null ,
[END_OF_WEEK_STARTING_FRI_DATE]		[datetime]	not null ,
[WEEK_STARTING_FRI_SEQ_NO]		[int]		not null ,


It sounds like your issues really revolve around fiscal or business periods specific for a particular company. For that situation, I recommend leaving the function alone, since it is really about calendar dates that are not specific to any company, and creating another table with info about fiscal years, months, weeks, etc.




CODO ERGO SUM
Go to Top of Page

rwaldron
Posting Yak Master

131 Posts

Posted - 11/09/2009 :  05:59:53  Show Profile  Reply with Quote
Hiya MVJ,
I have used your brilliant F_TABLE_DATE function for the last year wit great results..
However I now have a requirement to calculate business days EXCLUDING BANK HOLDAYS etc.
The way I could see this working is to create a table with a list of dates.These dates are to be excluded from the function.
Can you tell me how to encorporate this using your F_TABLE_DATE ?
Currently I use your func like below which gives me business days ie: not sat/sun.

f_get_work_days(startdate,enddate) as workdays
Go to Top of Page

AskQueAns
Starting Member

1 Posts

Posted - 05/11/2010 :  04:13:05  Show Profile  Reply with Quote
Hi
This function helps me alot and solve my whole problem except one i.e. how to get half_year_start_date and half_year_end date between 2 dadtes?

Please reply asap.
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

USA
474 Posts

Posted - 08/26/2010 :  15:19:20  Show Profile  Reply with Quote
I really like this function and have customized it quite a bit.

I have a couple of questions.

1. I'd like to include it in a blog post. Does Mr. Jones have a blog that he'd like me to link to or should I simply link to this thread?

2. Is there a compelling reason why the YearDayOfYear and YearMonth can't be a numeric type to facilitate easier format so the 256th day of 2010 would be:
2010.256 rather than 2010256

Thanks for such a great script.

Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/26/2010 :  15:46:54  Show Profile  Reply with Quote
Just link to this thread.

[YEAR], [MONTH], and [DAY_OF_YEAR] are already broken out to individual columns if you need. However, there is no reason why you can't customize your local version to suit your own needs.



CODO ERGO SUM
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

USA
474 Posts

Posted - 08/26/2010 :  15:53:38  Show Profile  Reply with Quote
Thank you Sir.
Go to Top of Page

MashUp
Starting Member

1 Posts

Posted - 10/07/2011 :  07:01:35  Show Profile  Reply with Quote
Has anyone seen similar in Oracle SQL?

Not everything that counts can be counted, and not everything that can be counted counts.
Go to Top of Page

MorrisK
Yak Posting Veteran

82 Posts

Posted - 02/08/2012 :  15:40:08  Show Profile  Reply with Quote
Thanks for the great function. I noticed something and I wondered if it could be an error.

The column DATE_FORMAT_YYYY_M_D appears to actually return YYYY/D/M.

It looks like this

[DATE_FORMAT_YYYY_M_D] =
convert(varchar(10),
convert(varchar(4),year(a.[DATE]))+'/'+
convert(varchar(2),day(a.[DATE]))+'/'+
convert(varchar(2),month(a.[DATE]))),

should be replaced with this

[DATE_FORMAT_YYYY_M_D] =
convert(varchar(10),
convert(varchar(4),year(a.[DATE]))+'/'+
convert(varchar(2),month(a.[DATE]))+'/'+
convert(varchar(2),day(a.[DATE]))),

Edited by - MorrisK on 02/08/2012 15:45:12
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 07/05/2012 :  02:55:33  Show Profile  Reply with Quote
quote:
Originally posted by MorrisK

Thanks for the great function. I noticed something and I wondered if it could be an error.

The column DATE_FORMAT_YYYY_M_D appears to actually return YYYY/D/M.

It looks like this

[DATE_FORMAT_YYYY_M_D] =
convert(varchar(10),
convert(varchar(4),year(a.[DATE]))+'/'+
convert(varchar(2),day(a.[DATE]))+'/'+
convert(varchar(2),month(a.[DATE]))),

should be replaced with this

[DATE_FORMAT_YYYY_M_D] =
convert(varchar(10),
convert(varchar(4),year(a.[DATE]))+'/'+
convert(varchar(2),month(a.[DATE]))+'/'+
convert(varchar(2),day(a.[DATE]))),



I made the suggested fix.


CODO ERGO SUM
Go to Top of Page

DennisTowers
Starting Member

USA
1 Posts

Posted - 10/09/2012 :  23:06:38  Show Profile  Reply with Quote
Best Darned Date Dimension, Ever! Thanks very much. Dennis.
Go to Top of Page

ienneg
Starting Member

France
3 Posts

Posted - 11/20/2012 :  12:19:16  Show Profile  Reply with Quote
Hi,
This is a great function and it helps a lot.
Now, that said, could you help add to it the ISO Quarter number?
Thanks
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/20/2012 :  23:58:17  Show Profile  Reply with Quote
quote:
Originally posted by ienneg

Hi,
This is a great function and it helps a lot.
Now, that said, could you help add to it the ISO Quarter number?
Thanks



I am unaware of any actual definition or standard for "ISO Quarter number". Do you have a link to a standard or definition?

Function F_TABLE_DATE does have a column for the calendar quarter, QUARTER, where months 1-3 are Q1, 4-6 are Q2, 7-9 are Q3 and 10-12 are Q4. There ia also a YEAR_QUARTER column in format YYYYQ, Example = 20052





CODO ERGO SUM
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.12 seconds. Powered By: Snitz Forums 2000