I enjoy writing T-SQL scripts, function and stored procedure for business but while working with SQL server date function it gets tricky, just keep it simple. Make sure that date column does not have time included. If it is include remove time from date using convert function. Whenever you insert date in table verify that datatype of column should match with date format. Different date function are available in SQL server to convert date in specific format
So while writing script when you face problem try SQL Server date functions. These function will helps you to convert date and time values to other date and time formats.
SQL Server Date Functions
Date function is use to extract some part of date while writing script. GETDATE() is popular function to provide current date, DATEDIFF, DATEPART and DATEADD are date function in SQL server
Lets understand these built-in date functions with their syntax and example
GETDATE() – It returns the current date and time of system
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.
SELECT DATEPART(month, GETDATE())
DATEADD() – Using this function, you add or subtract a specified time interval from a date
DATEDIFF()- Returns the time between two dates
CONVERT() – Converts date in different date formats
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
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.
Enjoy learning !!!!