SpyriadisNetworkBlob

Tag: Transformation

  • How to get day of the week from a datetime in SQL Server

    The day of the week can be retrieved in SQL Server by using the DatePart function. The value returned by function is between 1 (Sunday) and 7 (Saturday). To convert this to a string representing the day of the week, use a CASE statement.

    Method 1:
    Create function running following script:

    CREATE FUNCTION dbo.udf_DayOfWeek(@dtDate DATETIME)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @rtDayofWeek VARCHAR(10)
SELECT @rtDayofWeek = CASE DATEPART(weekday,@dtDate)
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 7 THEN 'Saturday'
END
RETURN (@rtDayofWeek)
END
GO

    Call this function like this:
    SELECT dbo.udf_DayOfWeek(GETDATE()) AS DayOfWeek
    ResultSet:
    DayOfWeek
    ———-
    Monday

    Method 2:

    SELECT DATENAME(dw, GETDATE())