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 ‘-‘.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s