SQL Server commonly used time functions---GETDATE, getutcdate, Datename

server|sqlserver| function SQL Server commonly used time functions---GETDATE, getutcdate, datename SQL Server commonly used time functions---GETDATE, getutcdate, Datename Author: Amoy Net Date: 2005-4-12 15:53:24 Visits: Origin: Amoy-Net--http://www.tot.name display options: Automatic scrolling [left button stop]getdate
Returns the current system date and time by the Microsoft®sql Server™ standard internal format of the datetime value.
Grammar
GETDATE ()
return type
Datetime
Comments
The date function can be used in the select list of the SELECT statement or in the WHERE clause of the query.

When you design a report, the GETDATE function can be used to print the current date and time each time a report is generated. GETDATE is also useful for tracking activities, such as recording the time that a transaction occurs on an account.
Example A. Returns the current date and time with get date
The following example draws the current system date and time:

SELECT GETDATE () go

Here is the result set:

-------------------------July 1998 2:50 PM (1 row (s) affected)
B. Using GETDATE in the CREATE TABLE statement
The following example creates a Employees table and uses GETDATE to give the default value of the employee's employment time.

Use pubsgocreate TABLE Employees (emp_id char (one) not NULL, emp_lname varchar (=) NOT NULL, Emp_fname varchar (m) NOT NULL , Emp_hire_date datetime DEFAULT GETDATE (), emp_mgr varchar) gogetutcdate
Returns a DateTime value that represents the current UTC time (world time coordinate or Greenwich Mean Time). The current UTC time is from the current local time and the time zone settings in the computer operating system running SQL Server.
Grammar
getUTCDate ()
return type
Datetime
Comments
getUTCDate is a nondeterministic function. Views and expressions referencing the column cannot be indexed.

getUTCDate cannot be invoked within a user-defined function.
Datename
Returns a string representing the specified date part of the specified date.
Grammar
Datename (datepart, date)
Parameters
DatePart

Is the parameter that specifies the part of the date that should be returned. The following table lists the date parts and abbreviations identified by the Microsoft®sql Server™.

Date part abbreviation Yearyy, YYYYQUARTERQQ, QMONTHMM, Mdayofyeardy, Ydaydd, Dweekwk, Wwweekdaydwhourhhminutemi, Nsecondss, Smillisecondms

The weekday (DW) Date section returns the day of the week (Sunday, Monday, and so on).

is an expression that returns a DateTime or smalldatetime value or a date format string. datetime data types are used for dates after January 1, 1753. Earlier dates are stored as character data. When you enter a datetime value, it is always placed in quotation marks. Because smalldatetime is only accurate to minutes, the seconds and milliseconds are always 0 when using smalldatetime values. For more information about a specified date, see DateTime and smalldatetime. For more information about time value designations, see time format.

If you specify only the last two digits of the year, the value of the last two digits of the value of the two digit year cutoff configuration option is less than or equal to the same century as the end of the century. The number of the last two digits that is greater than the value of this option is the century before the century that is the cutoff year. For example, if two digit year cutoff is 2050 (default), then 49 is interpreted as 2049, and 50 is interpreted as 1950. To avoid ambiguity, use a four-digit year.
return type
nvarchar
Comments
SQL Server automatically converts between character and datetime values as needed, for example, when comparing character values to datetime values.
Example
This example extracts the month name from the date returned by GETDATE.

SELECT datename (month, GETDATE ()) as ' month Name '

Here is the result set:

Month Name------------------------------February more:-> http://www.tot.name/show/3/