Function to get the next 1st thursday from the given day. Can be easily change to give the next1st any weekday.
If 1st thursday of the current month is passed , it gives the next month's 1st thursday.
sawmeonline,
2012-01-20 (first published: 2011-12-16)
Function to get the next 1st thursday from the given day. Can be easily change to give the next1st any weekday.
If 1st thursday of the current month is passed , it gives the next month's 1st thursday.
CREATE FUNCTION dbo.Getnext1stthursday(@currentDate DATETIME) RETURNS DATETIME AS BEGIN DECLARE @1stThurs DATETIME; DECLARE @daysToAdd INT = CASE WHEN ( 5 - Datepart(weekday, Dateadd(DD, 1 - Day(@currentDate), @currentDate)) ) < 0 THEN --Thursday Passed 7 + ( 5 - Datepart(weekday, Dateadd(DD, 1 - Day(@currentDate), @currentDate)) ) ELSE ( 5 - Datepart(weekday, Dateadd(DD, 1 - Day(@currentDate), @currentDate)) ) END SET @1stThurs = CONVERT(DATE, Dateadd(dd, @daysToAdd, Dateadd(DD, 1 - Day(@currentDate), @currentDate))); RETURN CASE WHEN @1stThurs < @currentDate THEN dbo.Getnext1stthursday(Dateadd(DD, 1 - Day(Dateadd(mm, 1, @currentDate)), Dateadd(mm, 1, @currentDate))) ELSE @1stThurs END; END
One of the times that you need things to go right is when you are doing analysis and reporting. This is generally based on time and date. A sure-fire way of getting managers upset is to get the figures horribly wrong by messing up the way that you handle datetime values in SQL Server. In the interests of peace, harmony and a long career in BI, Robert Sheldon outlines some of the worst mistakes you can make when using SQL Server dates.
2015-06-08
7,158 reads
This tip describes some T-SQL techniques for converting a mmddyyyy string to a SQL Server date, handling rows with missing date strings, and comparing converted date strings.
2015-05-28
7,705 reads
The handling of dates in TSQL is complex - when SQL Server was Sybase, it was forced by the lack of prevailing standards in SQL to create its own ways of processing and formatting dates and times. Joe Celko looks forward to a future when it is possible to write standard SQL date-processing code with SQL Server.
2014-03-21
7,283 reads
Greg Larsen explores the new SQL Server 2012 date and time functions and shows you how to exploit these functions in new application code.
2013-02-13
3,844 reads