T-SQL Tutorial

SQL Declare variable date


Declare variable date

To declare a date variable, use the DECLARE keyword, then type the @variable_name and variable type: date, datetime, datetime2, time, smalldatetime, datetimeoffset.
In the declarative part, you can set a default value for a variable. The most commonly used default value for a date variable is the function Getdate().
The function Getdate() returns the current database system timestamp as a datetime.
The datetime define a date that is combined with a time of day with fractional seconds.
The datetime2 is an extension of the datetime type that has a larger date range.
The time define a time of a day.

Example

USE model;
GO
DECLARE @date date = '04-18-2020';
DECLARE @curent_date date = getdate();
DECLARE @duedate date = getdate()+2;
SELECT @date AS 'Date', @curent_date AS 'Curent date', @duedate AS 'Due date';
GO

Result:

DateCurent dateDue date
2020-04-182020-04-202020-04-22




Declare variable date with time

USE model;
GO
DECLARE @date date= '04-20-2020';
DECLARE @datetime datetime = @date;
DECLARE @datetime2 datetime2 = '04-20-2020 10:17:30.123';
DECLARE @time time = @datetime2;
SELECT @datetime AS 'Datetime', @datetime2 AS 'Datetime2', @time AS 'Time';
GO

Result:

DatetimeDatetime2Time
2020-04-20 00:00:00.0002020-04-20 10:17:30.123000010:17:30.1230000