Journey to SQL Authority with Pinal Dave

Recently I have recieved email from Vivek Jamwal, which contains many useful SQL Server Date functions.
—-Today
SELECT GETDATE() ‘Today’
—-Yesterday
SELECT DATEADD(d,-1,GETDATE()) ‘Yesterday’
—-First Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) ‘First Day of Current Week’
—-Last Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) ‘Last Day of Current Week’
—-First Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) ‘First Day of Last Week’
—-Last Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) ‘Last Day of Last Week’
—-First Day of Current Month
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) ‘First Day of Current Month’
—-Last Day of Current Month
SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) ‘Last Day of Current Month’
—-First Day of Last Month
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) ‘First Day of Last Month’
—-Last Day of Last Month
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) ‘Last Day of Last Month’
—-First Day of Current Year
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) ‘First Day of Current Year’
—-Last Day of Current Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) ‘Last Day of Current Year’
—-First Day of Last Year
SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) ‘First Day of Last Year’
—-Last Day of Last Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) ‘Last Day of Last Year’
ResultSet:
Today
———————–
2008-08-29 21:54:58.967
Yesterday
———————–
2008-08-28 21:54:58.967
First Day of Current Week
————————-
2008-08-25 00:00:00.000
Last Day [...]

Read Full Post »

Datetime functions and stored procedures always interests me. Nanda Kumar has suggested modification to previous written article about SQL SERVER - SQL SERVER - UDF - Get the Day of the Week Function - Part 2. He has improved on UDF.
CREATE FUNCTION dbo.udf_DayOfWeek(@dtDate DATETIME)
RETURNS VARCHAR(10)
            AS
    BEGIN
    DECLARE @rtDayofWeek VARCHAR(10)
    DECLARE @weekDay INT
        —-Here I have subtracted 7 For keeping Sunday as the First day like wise for Monday we need to subtract 2 and so on
        SET @weekDay=((DATEPART(dw,@dtDate)+@@DATEFIRST-7)%7)
    SELECT @rtDayofWeek = CASE @weekDay
                    WHEN 1 THEN ’Sunday’
                    WHEN 2 THEN ’Monday’
                    WHEN 3 THEN ’Tuesday’
                    WHEN 4 THEN ’Wednesday’
                    WHEN 5 THEN ’Thursday’
                    WHEN 6 THEN ’Friday’
                    WHEN 0 THEN ’Saturday’
        END
    RETURN (@rtDayofWeek)
    END
GO
SELECT dbo.udf_dayofweek(GETDATE())

Reference : Pinal Dave (http://www.SQLAuthority.com), Nanda Kumar

Read Full Post »