SQL – How to get the day of the week

Using the DATEPART function to calculate the integer value of the day of the week.

select datepart(dw,getdate()) as DayOfWeekINT


Output:
DayOfWeekINT
————
2  

1 = Sunday
2 = Monday
3 = Tuesday
4 = Wednesday
5 = Thursday
6 = Friday
7 = Saturday

Using the DATENAME function to calculate the  day of the week.

select datename(dw,getdate()) as DayOfWeekCHAR


Output:
DayOfWeekCHAR
——————————
Monday
 
 

 

You can get the day of the week for a given date (MM/DD/YYYY) using:

select datepart(dw,’03/07/2011′) as DayOfWeekINT
select datename(dw,’03/07/2011′) as DayOfWeekCHAR

 

SQL – Date Formats – GETDATE() examples

The following table shows some of the most common date
formats available using the SQL function GETDATE().

The commands listed below can be used to store the date
value in a variable to be used on the scope of a script / stored procedure.

The following example with print the current date as
DD/MM/YYYY:

DECLARE @DATE AS VARCHAR(10)
SET @DATE = CONVERT(VARCHAR(10),GETDATE(),103)

PRINT @DATE

This example shows how to display yesterday’s date as
DD/MM/YYYY:

DECLARE @DATE AS VARCHAR(10)
SET @DATE = CONVERT(VARCHAR(10),GETDATE() 1, 103)

PRINT
@DATE

DATE ONLY

FORMAT

SQL COMMAND

MM/DD/YY

SELECT CONVERT(VARCHAR(8),GETDATE(),1)

MM/DD/YYYY

SELECT CONVERT(VARCHAR(10),GETDATE()
,
101)

YY.MM.DD

SELECT CONVERT(VARCHAR(8),GETDATE(),2)

YYYY.MM.DD

SELECT CONVERT(VARCHAR(10),GETDATE()
,
102)

DD/MM/YY

SELECT CONVERT(VARCHAR(8),GETDATE(),3)

DD/MM/YYYY

SELECT CONVERT(VARCHAR(10),GETDATE()
,
103)

DD.MM.YY

SELECT CONVERT(VARCHAR(8),GETDATE(),4)

DD.MM.YYYY

SELECT CONVERT(VARCHAR(10),GETDATE()
,
104)

DD-MM-YY

SELECT CONVERT(VARCHAR(8),GETDATE(),5)

DD-MM-YYYY

SELECT CONVERT(VARCHAR(10),GETDATE()
,
105)

DD Month YY

SELECT CONVERT(VARCHAR(9),GETDATE(),6)

DD Month YYYY

SELECT CONVERT(VARCHAR(11),GETDATE()
,
106)

Month DD, YY

SELECT CONVERT(VARCHAR(10),GETDATE(),7)

Month DD, YYYY

SELECT CONVERT(VARCHAR(12),GETDATE()
,
107)

MM-DD-YY

SELECT CONVERT(VARCHAR(8),GETDATE(),10)

MM-DD-YYYY

SELECT CONVERT(VARCHAR(10),GETDATE()
,
110)

YY/MM/DD

SELECT CONVERT(VARCHAR(10),GETDATE()
,110)

YYYY/MM/DD

SELECT CONVERT(VARCHAR(10),GETDATE(),
111)

YYMMDD

SELECT CONVERT(VARCHAR(6),GETDATE(),12)

YYYYMMDD

SELECT CONVERT(VARCHAR(8),GETDATE(),112)

TIME ONLY

FORMAT

SQL COMMAND

HH:MM:SS

SELECT CONVERT(VARCHAR(8),GETDATE(),108)

HH:MM:SS:Milli(24H)

SELECT CONVERT(VARCHAR(12),GETDATE()
,
114)

DATE AND TIME

FORMAT

SQL COMMAND

Month DD YYYY HH:MM[AM/PM]

SELECT CONVERT(VARCHAR(20),GETDATE(),100)

Month DD YYYY HH:MM:SS:Milli[AM/PM]

SELECT CONVERT(VARCHAR(26),GETDATE(),109)

DD Month YYYY HH:MM:SS:Milli (24h)

SELECT CONVERT(VARCHAR(24),GETDATE(),113)

DD Month YYYY HH:MM:SS:Milli(24h)

SELECT CONVERT(VARCHAR(24),GETDATE(),113)

YYYY-MM-DD HH:MM:SS (24h)

SELECT CONVERT(VARCHAR(19),GETDATE(),120)

YYYY-MM-DD HH:MI:SS.Milli(24h)

SELECT CONVERT(VARCHAR(23),GETDATE(),121)

SQL – How To get The Current Date Using GetDate()

The getDate() function is used to generate the current date.

print getDate()

This can be used with variables to create the date format required. The following code will set the string equivalent of the date format DD/MM/YY to the variable @date.

declare @date as nvarchar(50)
declare @day as nvarchar(50)
declare @month as nvarchar(50)
declare @year as nvarchar(50)
declare @zeroday as nvarchar(50)
declare @zeromonth as nvarchar(50)

— SET THE DATE
set @date         = cast(getDate() as char)
set @day          = day(@date)
if @day < 10
      set @zeroday = 0
else
      set @zeroday = ”
set @month  = month(@date)
if @month < 10
      set @zeromonth = 0
else
      set @zeromonth = ”
set @year         = year(@date)
set @date   = @zeroday + @day + ‘/’ + @zeromonth + @month + ‘/’ + right(@year,2)

print @date

To get yesterday’s date replace this line

set @date         = cast(getDate() as char)

with:

set @date         = cast(getDate() – 1 as char)

To get the date 1 week ago, use:

set @date         = cast(getDate() – 7  as char)

How To Get The Current Date In SQL Server

The following code returns the current date:

 

— VARIABLE DECLARATION

declare @date as nvarchar(50)

declare @day as nvarchar(50)

declare @month as nvarchar(50)

declare @year as nvarchar(50)

declare @zeroday as nvarchar(50)

declare @zeromonth as nvarchar(50)

 

 

— SET THE DATE

set @date         = cast(getDate() as char)

set @day          = day(@date)

if @day < 10

      set @zeroday = 0

else

      set @zeroday =

set @month  = month(@date)

if @month < 10

      set @zeromonth = 0

else

      set @zeromonth =

set @year         = right(year(@date),2)

set @date   = @zeroday + @day + ‘/’ + @zeromonth + @month + ‘/’ + @year

 

print @date

 

Part 1: Declare the variables to held the parts of the date.
Part 2: Set the key parts of the date into the variables. If the day or month is less than 10, then add a zero to the output.
e.g. If the date is 1st January 2009 we want the date to be displayed as 01/01/09 instead of 1/1/09.

Part 3: Set @date to the format required.
The following line specified the format of the date output.

set @date   = @zeroday + @day + ‘/’ + @zeromonth + @month + ‘/’ + @year

This will outout the date as DD/MM/YY.

if you want the format MM/DD/YY use the following format:

set @date   = @zeromonth + @month + ‘/’ + @zeroday + @day + ‘/’ + @year

You can change the delimiter of course, to display DD-MM-YY replace the ‘/’ with ‘-‘.