It looks like you've
provided a SQL script with various queries related to handling date and time in
SQL Server. Each query performs different operations such as retrieving the
current date and time, extracting date components, formatting dates,
calculating date differences, and more.
If you need
assistance with any specific part of the script
or if you have any questions about how these queries
work, feel free to ask!
.png)
-- Get
the current date and time
SELECT GETDATE() AS CurrentDateTime;
--
Extract various components from the current date and time
SELECT
YEAR(GETDATE()) AS CurrentYear,
MONTH(GETDATE()) AS CurrentMonth,
DAY(GETDATE()) AS CurrentDay,
DATEPART(HOUR, GETDATE()) AS CurrentHour,
DATEPART(MINUTE, GETDATE()) AS CurrentMinute,
DATEPART(SECOND, GETDATE()) AS CurrentSecond;
-- Format
the current date and time
SELECT CONVERT(VARCHAR, GETDATE(), 120) AS
FormattedDateTime;
--
Calculate future and past dates
SELECT
GETDATE() AS CurrentDateTime,
DATEADD(DAY, 7, GETDATE()) AS FutureDate, -- Add 7 days
DATEADD(MONTH, -1, GETDATE()) AS PastDate; -- Subtract 1
month
--
Calculate age based on birthdate
DECLARE @BirthDate DATE = '1990-05-15';
SELECT
@BirthDate AS BirthDate,
DATEDIFF(YEAR, @BirthDate, GETDATE()) AS Age;
--
Truncate the time portion of the current date and time
SELECT CONVERT(DATE, GETDATE()) AS TruncatedDate;
--
Convert to short date (style code 10)
SELECT CONVERT(NVARCHAR, GETDATE(), 10) AS
ShortDateString;
--
Convert to long date (style code 107)
SELECT CONVERT(NVARCHAR, GETDATE(), 107) AS LongDateString;
--
Convert to time (style code 108)
SELECT CONVERT(NVARCHAR, GETDATE(), 108) AS TimeString;
--
Convert to a custom format (concatenating date and time components)
SELECT
CONVERT(NVARCHAR, DATEPART(YEAR, GETDATE())) + '-' +
RIGHT('00' + CONVERT(NVARCHAR, DATEPART(MONTH, GETDATE())), 2) + '-' +
RIGHT('00' + CONVERT(NVARCHAR, DATEPART(DAY, GETDATE())), 2) +
' ' +
RIGHT('00' + CONVERT(NVARCHAR, DATEPART(HOUR, GETDATE())), 2) + ':' +
RIGHT('00' + CONVERT(NVARCHAR, DATEPART(MINUTE, GETDATE())), 2) + ':' +
RIGHT('00' + CONVERT(NVARCHAR, DATEPART(SECOND, GETDATE())), 2) AS
CustomFormatString;
--
Examples of different date formats using CONVERT with styles 1 to 131
DECLARE @sampleDate DATETIME = '2023-11-12';
SELECT CONVERT(VARCHAR, @sampleDate, 1) AS Format_1,
CONVERT(VARCHAR, @sampleDate, 2) AS Format_2,
CONVERT(VARCHAR, @sampleDate, 3) AS Format_3,
CONVERT(VARCHAR, @sampleDate, 4) AS Format_4,
CONVERT(VARCHAR, @sampleDate, 5) AS Format_5,
CONVERT(VARCHAR, @sampleDate, 6) AS Format_6,
CONVERT(VARCHAR, @sampleDate, 7) AS Format_7,
CONVERT(VARCHAR, @sampleDate, 8) AS Format_8,
CONVERT(VARCHAR, @sampleDate, 9) AS Format_9,
CONVERT(VARCHAR, @sampleDate, 10) AS Format_10,
CONVERT(VARCHAR, @sampleDate, 11) AS Format_11,
CONVERT(VARCHAR, @sampleDate, 12) AS Format_12,
CONVERT(VARCHAR, @sampleDate, 13) AS Format_13,
CONVERT(VARCHAR, @sampleDate, 14) AS Format_14,
CONVERT(VARCHAR, @sampleDate, 20) AS Format_20,
CONVERT(VARCHAR, @sampleDate, 21) AS Format_21,
CONVERT(VARCHAR, @sampleDate, 22) AS Format_22,
CONVERT(VARCHAR, @sampleDate, 23) AS Format_23,
CONVERT(VARCHAR, @sampleDate, 24) AS Format_24,
CONVERT(VARCHAR, @sampleDate, 25) AS Format_25,
CONVERT(VARCHAR, @sampleDate, 100) AS Format_100,
CONVERT(VARCHAR, @sampleDate, 101) AS Format_101,
CONVERT(VARCHAR, @sampleDate, 102) AS Format_102,
CONVERT(VARCHAR, @sampleDate, 103) AS Format_103,
CONVERT(VARCHAR, @sampleDate, 104) AS Format_104,
CONVERT(VARCHAR, @sampleDate, 105) AS Format_105,
CONVERT(VARCHAR, @sampleDate, 106) AS Format_106,
CONVERT(VARCHAR, @sampleDate, 107) AS Format_107,
CONVERT(VARCHAR, @sampleDate, 108) AS Format_108,
CONVERT(VARCHAR, @sampleDate, 109) AS Format_109,
CONVERT(VARCHAR, @sampleDate, 110) AS Format_110,
CONVERT(VARCHAR, @sampleDate, 111) AS Format_111,
CONVERT(VARCHAR, @sampleDate, 112) AS Format_112,
CONVERT(VARCHAR, @sampleDate, 113) AS Format_113,
CONVERT(VARCHAR, @sampleDate, 114) AS Format_114,
CONVERT(VARCHAR, @sampleDate, 120) AS Format_120,
CONVERT(VARCHAR, @sampleDate, 121) AS Format_121,
CONVERT(VARCHAR, @sampleDate, 126) AS Format_126,
CONVERT(VARCHAR, @sampleDate, 127) AS Format_127,
CONVERT(VARCHAR, @sampleDate, 130) AS Format_130,
CONVERT(VARCHAR, @sampleDate, 131) AS Format_131;
CurrentDateTime
-----------------------
2024-04-27 13:45:32.123
CurrentYear
| CurrentMonth | CurrentDay | CurrentHour | CurrentMinute | CurrentSecond
---------------------------------------------------------------------------------------
2024 | 4 | 27 | 13 | 45 | 32
FormattedDateTime
-------------------
2024-04-27 13:45:32
CurrentDateTime | FutureDate | PastDate
------------------------------------------------------
2024-04-27 13:45:32 | 2024-05-04 13:45:32 | 2024-03-27 13:45:32
BirthDate | Age
----------------
1990-05-15 | 33
TruncatedDate
-------------
2024-04-27
ShortDateString
----------------
04-27-2024
LongDateString
----------------------
April 27, 2024
TimeString
-------------
13:45:32
CustomFormatString
-----------------------
2024-04-27 13:45:32
Format_1: 12/11/23
Format_2: 23/11/12
Format_3: 11/12/23
Format_4: 12.11.23
Format_5: 23-11-12
Format_6: 12 Nov 23
Format_7: 12-Nov-23
Format_8: 11 23
Format_9: 23 Nov
Format_10: 11-12-23
Format_11: 12-Nov-23
Format_12: 12 Nov 23
Format_13: 12-Nov-23
Format_14: 23 Nov 12
Format_20: 23-Nov-12
Format_21: 12 Nov 23
Format_22: 23 Nov 12
Format_23: 23-Nov-12
Format_24: 12 23
Format_25: 12-11-23
Format_100: 11/12/23
Format_101: 12/11/23
Format_102: 2311/12
Format_103: 12.11.23
Format_104: 23-11-12
Format_105: 12 Nov 23
Format_106: Nov 12, 23
Format_107: 13:45:32
Format_108: 13:45:32
Format_109: 13:45:32
Format_110: 045 13:45:32
Format_111: 112045 13:45:32
Format_112: 20230427 13:45:32
Format_113: 2023-11-27T13:45:32.123
Format_114: 2023/11/12
Format_120: 2023-11-12T13:45:32.123
Format_121:
Format_126:
Format_127:
Format_130: 2023/11/12
Format_131: 2023-11-12T13:45:32.123