Feeds:
Posts
Comments

Posts Tagged ‘Days’


-- List of days in the week
;WITH WeekNames
AS
(
    SELECT datepart(weekday,getdate()) AS WeekNumber, getdate() AS ActualDate

    UNION ALL 

    SELECT datepart(weekday, dateadd(day, 1, b.ActualDate)), dateadd(day, 1, b.ActualDate)
    FROM  WeekNames b
    WHERE  DATEDIFF(day,getdate(), dateadd(day, 1, b.ActualDate)) < 7    
)
SELECT WeekNumber, dateName(weekday, ActualDate) WeekName  
FROM WeekNames
ORDER BY WeekNumber

-- List of the months 
;WITH MonthNames
as
(
    SELECT datepart(month, getdate()) AS MonthNumber, getdate() as ActualDate 

    UNION ALL 

    SELECT datepart(month, DATEADD(month, 1, mn.ActualDate)), dateadd(month, 1, mn.ActualDate) 
    FROM MonthNames mn
    WHERE datediff(month, getdate(), dateadd(month, 1, mn.actualDate)) < 12
)
SELECT MonthNumber, datename(month, ActualDate) as MonthName
FROM MonthNames
ORDER BY MonthNumber

--List of last 10 years 
;WITH Last10Years
as
(
    SELECT datepart(year, getdate()) AS YearNumber, getdate() as ActualDate 

    UNION ALL 

    SELECT datepart(year, DATEADD(year, -1, l10y.ActualDate)), dateadd(year, -1, l10y.ActualDate) 
    FROM Last10Years l10y
    WHERE datediff(year, dateadd(year, -1, dateadd(year, -1, l10y.ActualDate)), getdate()) <= 10
)
SELECT YearNumber
FROM Last10Years
ORDER BY YearNumber DESC 





Advertisements

Read Full Post »

--- Just a note ---
SELECT @@Datefirst -- By Default this is 7 which denotes Sunday and the values for Monday is 1 whihc cat be set if needed like below:
 -- set Datefirst 1
 ---------------

-- With Datepart we can retrieve the integer value of any part of a datetime value:
--    year, quarter, month, day of the year, day, week number, weekday number, hour, minute, second, or millisecond.
-- with datename we can retrieve its name / string value
-- Example:
SELECT datepart(week, getdate()) WeekNumber
 ,datepart(weekday, getdate()) WeekDayNumber
 ,datepart(month, getdate()) MonthDayNumber

--Week does not have a name so its sting value is returned
SELECT datename(week, getdate()) WeekName
 ,datename(weekday, getdate()) WeekDayName
 ,datename(Month, getdate()) MonthDayName

Result:

Read Full Post »

SELECT DAY(DATEADD (m, 1, DATEADD (d, 1 – DAY(GETDATE()), GETDATE())) – 1)

/*
1.    Find the current day of the month (let’s call it x)
2.    Subtract x from 1. This should give us a negative number (let’s call it y). Go back y days in the current month.
3.    This will give the 1st day in the month (let’s call it z)
4.    Add 1 month to z. This gives the first day of the next month. Subtract 1 from it and this gives us the last day of the current month.
5.    And that’s what we want!!
*/

Read Full Post »