tag:blogger.com,1999:blog-29286778.post115018771641054391..comments2023-07-02T05:53:35.132-04:00Comments on SQL Garbage Collector: Date Functions in SQL Server - FAQUnknownnoreply@blogger.comBlogger4125tag:blogger.com,1999:blog-29286778.post-84697382590575023042008-03-25T12:22:00.000-04:002008-03-25T12:22:00.000-04:00The Addworkingsdays is very usefull. Do you also h...The Addworkingsdays is very usefull. Do you also have a function who can make a date from a week and year, for the european calendar (datefirst=1). It hase to look like this "Create function Week2Date (@Week INT, @Year INT) as DATETIME....."<BR/>The problem I have, that I can't use the "Set datefirst 1" in a function, witch create a wrong week nr for some dates.<BR/>Regards<BR/>bosmbosmhttps://www.blogger.com/profile/13692495482631522061noreply@blogger.comtag:blogger.com,1999:blog-29286778.post-75256057275588782582007-10-26T23:20:00.000-04:002007-10-26T23:20:00.000-04:00I am not sure how you arrived at the consolidated ...I am not sure how you arrived at the consolidated query, but this is how it will be if I have to do it<BR/><BR/>select ((dateadd(mm,datediff(mm,0,'1 sep 2007') +1,0) - 1)-<BR/>((datepart(dw,dateadd(mm,datediff(mm,0,'1 sep 2007') +1,0) - 1)+ @@datefirst - 2)%7 + 1)/5 *<BR/>(((datepart(dw,dateadd(mm,datediff(mm,0,'1 sep 2007') +1,0) - 1)+ @@datefirst - 2)%7 + 1))%5)<BR/><BR/>The above query gives the value as 28th sept which is right. Let me know if I am wrong and I will be happy to correct it.<BR/>Regards<BR/>OmniOmnibuzzhttps://www.blogger.com/profile/04342835880399785393noreply@blogger.comtag:blogger.com,1999:blog-29286778.post-70646450354738783682007-10-26T09:49:00.000-04:002007-10-26T09:49:00.000-04:00select ((dateadd(mm,datediff(mm,0,'1 sep 2007') +1...select ((dateadd(mm,datediff(mm,0,'1 sep 2007') +1,0) - 1)-((datepart(dw,dateadd(mm,datediff(mm,0,'1 sep 2007') +1,0) - 1)+ @@datefirst - 2)%7 + 1)/5 *((datepart(dw,'1 sep 2007')+ @@datefirst - 2)%7 + 1)%5)<BR/><BR/>(consolidation of last working day of month in 1 stmt)<BR/><BR/>gives 29 sept, which is wrong, should be 28 septAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-29286778.post-35835711134839937212007-10-18T18:40:00.000-04:002007-10-18T18:40:00.000-04:00Love the function addworkingdays. How would it be...Love the function addworkingdays. How would it be done if you wanted to go back a number of days?Anonymousnoreply@blogger.com