In this topic we will learn how to use Date Functions in Sql Server with example Datetime and smalldatetime in SQL Server 2005 has two different data types used to store date and time data Date and time functions enable a programmer to get the system date, as well as to manipulate the date and time values stored in the database. Because date and time functions are useful in many different circumstances, it's difficult to emphasize one particular usage as being more essential than the others.
One complaint that you might hear is that SQL Server does not allow storing only the date or only the time—you must store both date and time in the same column if you use the DATETIME or SMALLDATETIME data type. Of course, you have the alternative of storing date values as strings, as in '1/1/2003'. Another alternative is to use the DATETIME data type and then use one of the date and time functions to retrieve only the needed portion (date or time) from the table.
DATEADD: DATEADD returns a new date that is incremented or decremented based on the interval and number specified.
DATEDIFF: DATEDIFFsubtracts the first date from the second date to produce a value in the format of the datepart code specified.
DATENAME : DATENAME returns a string value for the part of a date specified in the datepart code.
DATEPART : This function returns an integer value for the part of a date specified in the datepart code.
DAY, MONTH, and YEAR DAY : returns an integer value for the day, MONTH returns the integer representing the month, and YEAR returns the integer representing the year of the evaluated date.
GETDATE and CURRENT_TIMESTAMP : both return the current date and time. and CURRENT_TIMESTAMP returns the Greenwich Mean Time (Universal Time Coordinate).
Returning the Current Date and Time
GETDATE and CURRENT_TIMESTAMP both return the current date and time. This example demonstrates how to return the current date and time, as well as the Universal Time Coordinate (Greenwich Mean Time):
SELECT GETDATE(), Current Date and Time
CURRENT_TIMESTAMP, -- Current Date and Time
|
Examples:
- SELECT GETDATE() would return 2011-09-14 20:24:17.060
- SELECT "Todays date is" + CONVERT(varchar(12), GETDATE(),101)
- This date function query yields something like:
- Today's date is 01/01/2011
- Select Convert(varchar,Birthday,101)as Birthday from tbl_Emps Where Id=89
- This date function query yields result like this.