Some other functions that can be used for various purposes are DATEADD, DATEDIFF, DATENAME, DATEPART, DAY, GETDATE, MONTH, and YEAR. Here's some further detail on these functions as well as a code sample showing their use:
Dateadd: Returns a new datetime value based on adding an interval to the specified date.
Syntax: DATEADD ( datepart, number, date )
Datediff: Returns the number of date and time boundaries crossed between two specified dates.
Syntax: DATEDIFF ( datepart, startdate, enddate )
Datename: Returns a character string representing the specified datepart of the specified date.
Syntax: DATENAME ( datepart, date )
Datepart: Returns an integer representing the specified datepart of the specified date.
Syntax: DATEPART ( datepart, date )
Day: Returns an integer representing the day datepart of the specified date.
Syntax: DAY ( date )
Getdate: Returns the current system date and time in the Microsoft® SQL Server™ standard internal format for datetime values.
Syntax: GETDATE ( )
Month: Returns an integer that represents the month part of a specified date.
Syntax: MONTH ( date )
Year: Returns an integer that represents the year part of a specified date.
Syntax: YEAR ( date )
declare @datevar datetime
select @datevar = getdate()
/*Example for getdate() : getting current datetime*/
select getdate() [Current Datetime]
/*Example for dateadd : getting date 7 days from current datetime*/
select dateadd(dd, 7, @datevar) [Date 7 days from now]
/*Example for datediff : getting no of days passed since 01-01-2004*/
select datediff(dd,'20040101',@datevar) [No of days since 01-01-2004]
/*Example for datename : getting month name*/
select datename(mm, @datevar) [Month Name]
/*Example for datepart : getting week from date*/
select datepart(wk, @datevar ) [Week No]
/*Example for day : getting day part of date*/
select day (@datevar) [Day]
/*Example for month : getting month part of date*/
select month(@datevar) [Month]
/*Example for year : getting year part of date*/
select year(@datevar) [Year]
Partager