Technical: Date functions & Date format in SQL Server

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

3. See below example show how to get Date only from DateTime

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*/