InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 17:17:12 GMT20http://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspx[quote][b]ScottPletcher (12/3/2012)[/b][hr][quote]CELKO (12/1/2012)Please learn to use the ISO-8601 date formats; it is the only one allowed in Standard SQL and the other ISO Standards. Use the DATEADD() function for this. [/quote][b]Or, for your own sake, DON'T[/b].The safe format to use in SQL Server is 'YYYYMMDD', which is [i]always[/i] interpreted correctly, rather than 'YYYY-MM-DD', which [b]can cause abends[/b].When an error occurs at 3AM because of this, I don't think anyone will be happy just because you followed some arbitrary theoretical standard, rather than using an error-proof method for the actual db you are using.[/quote]+1!!!!Tue, 04 Dec 2012 16:49:13 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspxThanks Lynn, got it. Safer is Better,Tue, 04 Dec 2012 13:19:56 GMTPete Coxhttp://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspx[quote][b]Pete Cox (12/4/2012)[/b][hr]YYYYMMDD is in fact the Basic date format proposed by ISO8601.YYYY-MM-DD is named the Extended format.Using either is OK by the standard.[/quote][b]Excellent point[/b], and quite correct (I just researched it).And it means that Celko's oft-repeated claim that 'yyyy-mm-dd' is the "only" valid ISO format is 100% false.Tue, 04 Dec 2012 10:00:03 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspx[quote][b]Pete Cox (12/4/2012)[/b][hr]YYYYMMDD is in fact the Basic date format proposed by ISO8601.YYYY-MM-DD is named the Extended format.Using either is OK by the standard.[/quote]True, but YYYY-MM-DD doesn't always work depending on the setting of DATEFORMAT, whereas YYYYMMDD will always be correctly converted to datetime values (at least for now).Tue, 04 Dec 2012 07:51:00 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspx[quote][b]kapil_kk (12/3/2012)[/b][hr]I will try with Tally table and tally calender which Jeff has suggested....Thnks for the suggestion :-)[/quote]To be clear and to give credit where credit is due, I wasn't the first on this thread to suggest either. I was just providing a little concurrence for those that did.I believe that a Calendar table will proably suite you the best for these types of thigs in the long run. If you have difficulty in building one or using it, c'mon back with another post and we'll give you a leg up.Tue, 04 Dec 2012 07:37:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspxYYYYMMDD is in fact the Basic date format proposed by ISO8601.YYYY-MM-DD is named the Extended format.Using either is OK by the standard.Tue, 04 Dec 2012 01:39:40 GMTPete Coxhttp://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspx[quote]CELKO (12/1/2012)Please learn to use the ISO-8601 date formats; it is the only one allowed in Standard SQL and the other ISO Standards. Use the DATEADD() function for this. [/quote][b]Or, for your own sake, DON'T[/b].The safe format to use in SQL Server is 'YYYYMMDD', which is [i]always[/i] interpreted correctly, rather than 'YYYY-MM-DD', which [b]can cause abends[/b].When an error occurs at 3AM because of this, I don't think anyone will be happy just because you followed some arbitrary theoretical standard, rather than using an error-proof method for the actual db you are using.Mon, 03 Dec 2012 10:22:43 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspxI will try with Tally table and tally calender which Jeff has suggested....Thnks for the suggestion :-)Mon, 03 Dec 2012 07:43:06 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspxGiven a Calendar table something like this one(I only allow NULLS for the contruction phase of the table)CREATE TABLE [dbo].[Calendar]( [CalendarDate] [smalldatetime] NOT NULL, ... [DayName] [varchar](9) NULL, [ShortDayName] [char](3) NULL, ... [EpochSundayCount] [int] NULL, [EpochBusinessDayCount] [int] NULL,PRIMARY KEY CLUSTERED ( [CalendarDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]the following T-SQL would work to set the quasi-Julian dates.UPDATE Calendar SET EpochBusinessDayCount = DATEDIFF(day, (SELECT MIN(CalendarDate) from Calendar), CalendarDate) + 1 - (SELECT COUNT(CalendarDate) FROM Calendar c2 WHERE ShortDayName = 'Sun' and c2.CalendarDate <= c1.CalendarDate), EpochSundayCount = (SELECT COUNT(CalendarDate) FROM Calendar c2 WHERE ShortDayName = 'Sun' and c2.CalendarDate <= c1.CalendarDate) FROM Calendar c1 I've chosen to start from the earliest date in the calendar table and just keep rolling, so as to avoid the problem of crossing year boundaries.Mon, 03 Dec 2012 07:37:48 GMTPete Coxhttp://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspx[quote][b]kapil_kk (12/3/2012)[/b][hr]in this case it will be '2012-02-03'[/quote]Care to answer all the other questions I asked? I really don't feel like retyping them.Mon, 03 Dec 2012 07:05:32 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspxI'm with CELKO - use a calendar table, then you can count your Sundays, weekends, Easter Mondays and whatever else you need between two dates - much much easier, and kind on processing too.For an example calendar, you can check out:[url]http://www.kimballgroup.com/data-warehouse-and-business-intelligence-resources/data-warehouse-books/booksmdwt/[/url] (go to Chapter 7—Design and Develop the ETL System; date dimension) - Direct Link for DDL and sample data:[url]http://www.kimballgroup.com/wp-content/uploads/2012/07/Ch07_Date_Dim_2000-2020.xlsx[/url]HTH,BMon, 03 Dec 2012 06:51:48 GMTbleroyhttp://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspxUsing Joe's Calendar table, this might provide a way for the kapil to get to the answer he wants.It removes the need to recursively check if you added another sunday in the extension of the date periodSELECT cal_date FROM CalendarWHERE julian_business_nbr = (SELECT julian_business_nbr + 30 FROM Calendar WHERE cal_date = '2007-04-05')Mon, 03 Dec 2012 06:09:48 GMTPete Coxhttp://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspx[quote][b]kapil_kk (12/3/2012)[/b][hr]Jeff is right...I dont have any knowledge of Tally Table..[/quote]Please see the following article. A Tally Table (or Numbers table, as some call it) is a simple table of sequential integers used to replace a WHILE loop. [url]http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]Joe is correct though. A Calendar table (pretty obvious what that is) would do better here. I'm on my way to work so don't have the time to demo that kind of solution now but I'll check on this post when I get home.Mon, 03 Dec 2012 05:58:30 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspxJeff is right...I dont have any knowledge of Tally Table..Mon, 03 Dec 2012 05:51:40 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspx[quote][b]Jason-299789 (12/3/2012)[/b][hr]Using a Tally Table would improve this code significantly, also you have to be careful about using the fucntion datepart(dw,a_date) as in some cases Sunday isnt always represented by 1, and will depend on your regional settings.Eg : In the US, DW 1 is Sunday, yet in europe it can be DW 1 is monday.See books on line [url]http://msdn.microsoft.com/en-us/library/ms174420.aspx[/url]To mitigate this you need to use the SET DATEFIRST to make it consistent across all regional settings, see [url]http://msdn.microsoft.com/en-us/library/ms181598.aspx[/url][/quote]Jason,I know it sounds strange to those of us that use a Tally Table on a regular basis, but there are still a lot of folks that don't know what it is. You either have to explain, provide a link, or provide the full code (preferably some combination of those) for people that don't understand.Mon, 03 Dec 2012 05:49:59 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspxAnyone know if Joe's querySELECT (C2.julian_business_nbr - C1.julian_business_nbr) FROM Calendar AS C1, Calendar AS C2 WHERE C1.cal_date = '2007-04-05', AND C2.cal_date = '2007-04-10'; causes a cross join before the complete WHERE clause is applied ?Or are we just cross joining the 2 rows that would be the answer sets of 2 discreet queries, each using one of the phrases of the where clause?Mon, 03 Dec 2012 05:45:00 GMTPete Coxhttp://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspxUsing a Tally Table would improve this code significantly, also you have to be careful about using the fucntion datepart(dw,a_date) as in some cases Sunday isnt always represented by 1, and will depend on your regional settings.Eg : In the US, DW 1 is Sunday, yet in europe it can be DW 1 is monday.See books on line [url]http://msdn.microsoft.com/en-us/library/ms174420.aspx[/url]To mitigate this you need to use the SET DATEFIRST to make it consistent across all regional settings, see [url]http://msdn.microsoft.com/en-us/library/ms181598.aspx[/url]Mon, 03 Dec 2012 03:51:02 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspxI have created this script but looking for more optimization regarding performance--DECLARE @count intSET @count = 30DECLARE @sundays intSET @sundays = 0DECLARE @startdate datetimeSET @startdate ='2012-01-01'DECLARE @enddate datetimeSET @enddate = DATEADD(DD,@count,@startdate)PRINT @enddate PRINT 'Before Sunday'WHILE @startdate <= @enddateBEGINIF DATEPART(DW,@startdate) = 1BEGINSET @sundays = @sundays + 1SET @enddate = DATEADD(DD,1,@enddate)PRINT @enddatePRINT 'After Sunday'PRINT @sundaysENDSET @startdate = DATEADD(DD,1,@startdate)ENDMon, 03 Dec 2012 03:22:59 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspxin this case it will be '2012-02-03'Mon, 03 Dec 2012 03:21:59 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspxAnother question, startdate is 2012-12-02 (a Sunday) and you add 1 day, do you want 2012-12-03 or 2012-12-04? What if you add 0 days to the same date? Will you every add a negative number of days (i.e. go backwards from a given date)?Sun, 02 Dec 2012 08:54:49 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspxAs I think about this more, your apparent requirement is that everytime you cross Sunday you need to add an additional day, correct?Example, if the startdate is 2012-11-30 (a Friday) and you add 3 days you would normally get 2012-12-03 (a Monday). What you want, however, is to get 2012-12-04 (a Tuesday). Is this correct?Edit: And you probably want this to work over a year boundary as well, correct?Sun, 02 Dec 2012 08:40:15 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspx[quote][b]Lynn Pettis (12/2/2012)[/b][hr][quote][b]kapil_kk (12/2/2012)[/b][hr]the script you have written is not satisfy the scenario that i wrote..[/quote]You are going to have to tell me more than that it doesn't work. If I give it 2012-01-01 and 30 days, it returns 2012-02-04, just like you posted.Maybe it doesn't satisify your actual requirements, but you really aren't giving us much to work with. We need the DDL (CREATE TABLE) statement for your table, some sample data as a seried of INSERT INTO statements, and the expected results based on the sample data. With that information you will get a better answer.[/quote]This appears to work if you stay in the same year:[code="sql"]declare @TestDate date = '20120201', @DaysToAdd int = 30;select @TestDate, @DaysToAdd, dateadd(dd, @DaysToAdd, @TestDate), datepart(wk,dateadd(dd, @DaysToAdd, @TestDate)), datepart(wk,@TestDate), dateadd(dd, datepart(wk,dateadd(dd, @DaysToAdd, @TestDate)) - datepart(wk,@TestDate), dateadd(dd, @DaysToAdd, @TestDate));[/code]Sun, 02 Dec 2012 08:34:25 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspx[quote][b]kapil_kk (12/2/2012)[/b][hr]the script you have written is not satisfy the scenario that i wrote..[/quote]You are going to have to tell me more than that it doesn't work. If I give it 2012-01-01 and 30 days, it returns 2012-02-04, just like you posted.Maybe it doesn't satisify your actual requirements, but you really aren't giving us much to work with. We need the DDL (CREATE TABLE) statement for your table, some sample data as a seried of INSERT INTO statements, and the expected results based on the sample data. With that information you will get a better answer.Sun, 02 Dec 2012 08:21:30 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspxthe script you have written is not satisfy the scenario that i wrote..Sun, 02 Dec 2012 07:54:19 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspxyes you are right, I am trying to add 30 to a given date to get a x later date and want to count sunday in that date range and thn again add count of those sunday in that x date...Sun, 02 Dec 2012 07:46:54 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspxBased on the original post, I'm thinking you want something like this:[code="sql"]declare @TestDate date = '20120101', @DaysToAdd int = 8;select @TestDate, @DaysToAdd, dateadd(dd, @DaysToAdd, @TestDate), datepart(wk,dateadd(dd, @DaysToAdd, @TestDate)), dateadd(dd, datepart(wk,dateadd(dd, @DaysToAdd, @TestDate)) - 1, dateadd(dd, @DaysToAdd, @TestDate));[/code]Sat, 01 Dec 2012 21:30:43 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspxI'm not quite sure what you are asking for here. Are you trying to add 30 days to a given date such that you get a date x days later but skips Saturdays/Sundays?Sat, 01 Dec 2012 21:14:10 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspx[quote] I have a scenario in which I need to create a function which takes 2 parameter (displacement_count, start_date). If I have a count of 30 and start date is 2012-01-01 then 30 should get added to start_date and it will return 2012-01-31. [/quote]Please learn to use the ISO-8601 date formats; it is the only one allowed in Standard SQL and the other ISO Standards. Use the DATEADD() function for this. [quote]Now I need to calculate the count of Sundays between 2012-01-01 and 2012-01-31. Suppose I have 4 Sundays then I need to add 4 in date 2012-01-31 which will result in 2012-02-04, now again i have to calculate that is there any Sunday between 2012-01-31 and 2012-02-04 [/quote]SQL is a database language, so we use tables. The one you need for problem a calendar table with a Julianized date number. Here is a version that counts business days, change it for your purposes. CREATE TABLE Calendar(cal_date DATE NOT NULL PRIMARY KEY, julian_business_nbr INTEGER NOT NULL, ...);INSERT INTO Calendar VALUES ('2007-04-05', 42), ('2007-04-06', 43), -- good Friday ('2007-04-07', 43), ('2007-04-08', 43), -- Easter Sunday ('2007-04-09', 44), ('2007-04-10', 45); --TuesdayTo compute the business days from Thursday of this week to nextTuesdays:SELECT (C2.julian_business_nbr - C1.julian_business_nbr) FROM Calendar AS C1, Calendar AS C2 WHERE C1.cal_date = '2007-04-05', AND C2.cal_date = '2007-04-10'; Sat, 01 Dec 2012 21:13:36 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspxTake a look at this article by Lynn Pettis to see if that helps: [url]http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url]RobSat, 01 Dec 2012 21:06:44 GMTrobert.gerald.taylorhttp://www.sqlservercentral.com/Forums/Topic1391686-392-1.aspxHi, I have a scenario in which i need to create a function which takes 2 parameter (count,startdate).If I have a count of 30 and start date is 1/1/2012 then 30 should get added to atsrt date and it will return 31/1/2012. No i need to caculate no. of sundays between 1/1/2012 and 31/1/2012. Suppose i have 4 sundays then i need to add 4in date 31/1/2012 which will result in 4/2/2012 , now again i have to calcualte that is there any sunday between 31/1/2012 and 4/2/2012 and so on.........plz help me on this...Sat, 01 Dec 2012 19:47:43 GMTkapil_kk