My shared items

 

Saturday, September 1, 2007

SQL Server DateTime Related...

CONVERT

CONVERT function explicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality.

Syntax

CAST ( expression AS data_type )

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Arguments

expression

Is any valid Microsoft SQL Server expression.

data_type

Is the target system-supplied data type, including bigint and sql_variant. User-defined data types cannot be used.

length

Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types.

style

Is the style of date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or the string format when converting float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).


yy yyyy
Standard

Input/Output
- 0 or 100
Default mon dd yyyy hh:miAM (or PM)
1 101 USA mm/dd/yy
2 102 ANSI yy.mm.dd
3 103 British/French dd/mm/yy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
6 106 - dd mon yy
7 107 - Mon dd, yy
8 108 - hh:mm:ss
- 9 or 109
Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)
10 110 USA mm-dd-yy
11 111 JAPAN yy/mm/dd
12 112 ISO yymmdd
- 13 or 113
Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
- 20 or 120
ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
- 21 or 121
ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)
- 126 ISO8601 yyyy-mm-dd Thh:mm:ss:mmm(no spaces)
- 130 Kuwaiti dd mon yyyy hh:mi:ss:mmmAM
- 131 Kuwaiti dd/mm/yy hh:mi:ss:mmmAM

Some Other Date Related Functions...

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 )

Here,

datepart--> may be mm, dd, yy, yyyy, mi..etc...


Example:


PRINT DATEADD ( dd, 60, 'Sep 1 2007 10:46AM')


will prints
Oct 31 2007 10:46AM

No comments: