We use lot of date functions in day today coding in SQL Server world. So, thought of preparing a quick reference of commonly used date functions.
1. To get the current date use GetDate() function
2. To get UTC date use GetUTCDate() function
Select CONVERT(DATE , Getdate()) AS DateOnly
4. This example show how to get Time only from DateTime
Select CONVERT(TIME , Getdate()) AS TimeOnly
5. Following example show how to format date
SELECT FORMAT(GETDATE(), 'HH:mm:ss') AS TimeUsingFormat
6. Use DatePart function to get specific value from Date.
a. Year only
SELECT DATEPART(yyyy,GETDATE())
b. Month only
-- To get Month
SELECT DATEPART(mm, GETDATE())
-- To get Month amended with '0'
SELECT
FORMAT(GETDATE() , 'MM')
c. To display Month Name
-- To get MonthName
SELECT Format(GETDATE() , 'MMMM') AS NameoftheMonth
-- To get MonthName (3 Character
only)
SELECT Format(GETDATE() , 'MMM') AS NameoftheMonth
d. Day only
-- To get Day
SELECT DATEPART(dd, GETDATE())
-- To get Day amended with '0'
SELECT
FORMAT(DATEPART(dd, GETDATE()) - 15, '00')
e. Year and Month using Format function
-- To get Month and Year
SELECT Format(GETDATE() , 'mmyyyy') AS MonthAndYear
f. The below script will scan "myTable" table from StartDate to EndDate (whatever date range I choose), and iterate through this date-range, and if a record does not exist with any of the dates in this date-range, insert a new record with that particular date in the Timestamp field, but empty/NULL data for the rest of the fields.
DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @CurrentDate AS DATETIME
SET @StartDate = '2015-01-01'
SET @EndDate = GETDATE()
SET @CurrentDate = @StartDate
WHILE (@CurrentDate <
@EndDate)
BEGIN
SELECT * FROM myTable WHERE myTable.Timestamp
= "@CurrentDate"
IF @@ROWCOUNT < 1
print @CurrentDate
/*insert a new row query here*/
SET @CurrentDate =
convert(varchar(30), dateadd(day,1, @CurrentDate), 101); /*increment current
date*/
END
FYI - Starting for SQL Server 2012 version, you can use
FORMAT function.
-----------------------------------------------------------------------------------------------------------
Date and Time Conversions Using SQL Server
https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/
DatePart Function
----------------------------------------------------------------------------------------------------------------------------
Format SQL Server Dates with FORMAT Function
https://www.mssqltips.com/sqlservertip/2655/format-sql-server-dates-with-format-function/
https://www.mssqltips.com/sqlservertip/2655/format-sql-server-dates-with-format-function/
More about Conversion function
https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
---------------------------------------------------------------------------------------------------
Date Format
Select CONVERT(VARCHAR(10), Getdate(), 112)
AS fmtUTCDate
Conversion
----------------------------------------------------------------------------------------------------------------------------
DatePart
SELECT DATEPART(yyyy,'2007-10-30
12:15:32.1234567 +05:10')
SELECT DATEPART(mm,'2007-08-09
12:15:32.1234567 +05:10')
SELECT DATEPART(dd,'2007-10-30
12:15:32.1234567 +05:10')
