SQL Server Date Functions
While working with SQL Server Date function and to keep it simple, make sure that date column does not have time portion. Whenever you insert date in table verify that datatype of column should match with date format.
SQL Server Functions helps you to convert date and time values to other date and time formats.
There are some most important built-in date functions in SQL Server
GETDATE()
It returns the current date and time of system
Syntax
SELECT GETDATE()
DATEPART()
It will return single part of a date time like you can get year, date, hour, minute, month and so on from given date.
Syntax
SELECT DATEPART(month, GETDATE())
DATEADD()
Using this function, you add or subtract a specified time interval from a date
Syntax
SELECT DATEADD(datepart,nunber,date)
DATEDIFF()
Returns the time between two dates
Syntax
SELECT DATEDIFF(datepart,startdate,enddate)
CONVERT()
Converts date in different date formats
Syntax
SELECT CONVERT(datatype(length),experssion,format)
SQL Server has built-in datatype for storing date or date/time in the table.
DATE – Only date format YYYY-MM-DD
DATETIME – Date time format: YYYY-MM-DD HH:MI:SS
SMALLDATETIME – Small date time format: YYYY-MM-DD HH:MI:SS
TIMESTAMP – Generates unique number
Example
Lets try an example to understand the working of date datatype while querying the table.
SELECT Name, address, empid FROM employee_details WHERE Joining_date = ‘2008-15-04’
Result is as expected only if datatype of column Joining_date is DATE, If it is datetime or some other datatype then it returns no result.