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:
| Date | Curent date | Due date |
|---|---|---|
| 2020-04-18 | 2020-04-20 | 2020-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:
| Datetime | Datetime2 | Time |
|---|---|---|
| 2020-04-20 00:00:00.000 | 2020-04-20 10:17:30.1230000 | 10:17:30.1230000 |
