Using Date function with getdate()
-
Guys,
I have unique scenario here where I have to populate a column with first businees day of the year from years 1800 to 2005, I have been trying to use date function with getdate() but it doesnt seem to help.
My query returns only the first Monday of each year but as you first mon might be Jan 1st in which case it cannot be a business day.
Any suggestions/input would help
Thanks
-
Can you post your definition of the first business day of the year??
-
this assumes that jan 1st and 2nd are holidays and that jan 1st can be a Saturday (NDays.PkNumber between 3 and 5)
SELECT
MIN(dtDates.Date) AS FirstBusinessDays
FROM (
SELECT
DATEADD(YY, NYears.PkNumber - 1900, NDays.PkNumber - 1) AS Date
FROM dbo.Numbers NYears CROSS JOIN
dbo.Numbers Ndays
WHERE NYears.PkNumber BETWEEN 1800 and 2100 AND
NDays.PkNumber BETWEEN 3 AND 5 --this assumes that jan 1st and 2nd are holidays and that jan 1st can be a Saturday
 
) dtDatesWHERE DATEPART(DW, dtDates.Date) < 6
GROUP BY YEAR(dtDates.Date)
the numbers table :
IF Object_id('Numbers') > 0
DROP TABLE dbo.Numbers
GO
CREATE TABLE dbo.Numbers (PkNumber int identity(1,1) primary key clustered, dude bit null)
GO
INSERT INTO dbo.Numbers (dude)
SELECT NULL FROM (SELECT TOP 100 NULL AS A FROM master.dbo.spt_values) dt100 cross join (SELECT TOP 80 null AS A FROM master.dbo.spt_values) dt80
GO
ALTER TABLE dbo.Numbers
DROP COLUMN dude
GO
--Select min(PkNumber) as MinA, max(PkNumber) as MaxA from dbo.Numbers
--1, 8000
GO
--DROP TABLE dbo.Numbers
-
First business day is day which is not weekend not it is Jan 1st. So pretty much it is a first normal working/business day of the week
Thanks
-
Just change NDays.PkNumber BETWEEN 3 AND 5
to NDays.PkNumber BETWEEN 2 AND 4
and that'll work.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply