SQL Server Date function collection and collation

Common date functions

SQL Server time and date function detailed, SQL Server, time, date,

1. Current system date, time

Select GETDATE ()

2. DateAdd returns a new datetime value on the basis of adding a period of time to the specified date

For example: Add 2 days to date

Select DATEADD (day,2, ' 2004-10-15 ')--back: 2004-10-17 00:00:00.000

3. DateDiff returns the number of date and time boundaries across two specified dates.

Select DateDiff (Day, ' 2004-09-01 ', ' 2004-09-18 ')--return: 17

Select DateDiff (Day, ' 2004-09-18 ', ' 2004-09-01 ')--return:-17

4. DatePart returns an integer representing the specified date part of the specified date.

SELECT DATEPART (month, ' 2004-10-15 ')--return 10

5. Datename returns a string representing the specified date part of the specified date

SELECT Datename (Weekday, ' 2004-10-15 ')--return: Friday

6. Day (), month (), year ()--can be compared with datepart

Select Current date =convert (varchar), GETDATE (), 120)

, Current time =convert (varchar (8), GETDATE (), 114)

Select Datename (DW, ' 2004-10-15 ')

Select how many weeks of the year =datename (week, ' 2004-10-15 ')

, today is the week =datename (weekday, ' 2004-10-15 ')

function parameters/Functions

GetDate () returns the current date and time of the system

DateDiff (INTERVAL,DATE1,DATE2) returns the difference between Date2 and date1 two dates, as specified by interval

Date2-date1

DATEADD (interval,number,date) in the manner specified by interval, plus the date after number

DatePart (interval,date) returns the integer value of the specified part of the date, interval

Datename (Interval,date) returns the string name of the specified part of the date, interval

The set values for the parameter interval are as follows:

Value abbreviations (SQL Server) (Access and ASP) description

Year Yy yyyy 1753 ~ 9999

Quarter Qq Q Season 1 ~ 4

Month Mm M Month 1 ~ 12

Day of the year Dy y, number of days of the year, 1-366

Day Dd D, 1-31

Weekday Dw W number of days in a week, day of the week 1-7

Week Wk WW Week, the first weeks of the year 0 ~ 51

Hour Hh H 0 ~ 23

Minute Mi N min 0 ~ 59

Second Ss S SEC 0 ~ 59

Millisecond ms-milliseconds 0 ~ 999

Access and ASP Use Date () and now () to obtain the system datetime, where Datediff,dateadd,datepart is also used to

In Access and ASP, the use of these functions is similar

Example:

1.GetDate () for SQL Server:select GetDate ()

2.DateDiff (' s ', ' 2005-07-20 ', ' 2005-7-25 22:56:32 ') returns a value of 514,592 seconds

DateDiff (' d ', ' 2005-07-20 ', ' 2005-7-25 22:56:32 ') returns a value of 5 days

3.DatePart (' W ', ' 2005-7-25 22:56:32 ') returns a value of 2 i.e. Monday (Sunday is 1, Saturday is 7)

DatePart (' d ', ' 2005-7-25 22:56:32 ') returns a value of 25, or 25th

DatePart (' y ', ' 2005-7-25 22:56:32 ') returns a value of 206 that is the No. 206 Day of the Year

DatePart (' yyyy ', ' 2005-7-25 22:56:32 ') returns a value of 2005 i.e. 2005

The specific syntax:

Date functions are used to manipulate data of datetime and smalldatetime types and perform arithmetic operations. As with other functions, you can

Select and WHERE clauses of the SELECT statement and the use of date functions in expressions. The following methods are used:

A date function argument in which the number of parameters varies by function.

· Day ()

The day () function syntax is as follows:

Day (<date_expression>)

The day () function returns the date value in Date_expression.

· MONTH ()

The MONTH () function syntax is as follows:

MONTH (<date_expression>)

The MONTH () function returns the value of the month in Date_expression.

Unlike the day () function, the MONTH () function, when the argument is an integer, returns an integer value of 1, which is what SQL Server considers its

It's January 1900.

· Year ()

The year () function syntax is as follows:

Year (<date_expression>)

The year () function returns the value of years in Date_expression.

Reminder: When using a date function, the date value should be between 1753 and 9999, which is the date that the SQL Server system recognizes

Wai, otherwise there will be errors.

· DATEADD ()

The DATEADD () function syntax is as follows:

DATEADD (<DATEPART>, <number>, <date>)

The DATEADD () function returns a new date that is generated by the specified date plus the specified extra date interval number. Parameter "DatePart

"is often used in date functions to specify the components that make up date type data, such as year, quarter, month, day, week, and so on."

The values are as shown in table 4-9:

· DATEDIFF ()

The DATEDIFF () function syntax is as follows:

DATEDIFF () (<DATEPART>, <date1> <date2>)

The DATEDIFF () function returns the difference between the datepart of two specified dates, that is, the date2 exceeds the date1 gap value, and its

The result value is an integer value with a positive sign. For different datepart, the maximum gap value allowed by the DATEDIFF () function is not

, such as: When DatePart is second, the maximum gap allowed by the DATEDIFF () function is 68: Year DatePart is

Millisecond, the maximum gap allowed by the DATEDIFF () function is 24 days, 20 hours, 30 minutes, 23 seconds, and 647 milliseconds.

· Datename ()

The Datename () function syntax is as follows:

Datename (<datepart>, <date) >

The Datename () function returns the specified portion of a date in the form of a string. specified by DatePart.

· DATEPART ()

The DATEPART () function syntax is as follows:

DATEPART (<DATEPART> <date>)

The DATEPART () function returns the specified portion of a date as an integer value. This section is specified by DatePart.

DATEPART (dd, date) is equivalent to day (date)

DATEPART (mm, date) is equivalent to month (date)

DATEPART (yy, date) is equivalent to year (date)

· GETDATE ()

The GETDATE () function syntax is as follows:

GETDATE ()

The GETDATE () function returns the current date and time of the system in the default format of DateTime, which is often used as a parameter to another function or command

Number of uses.

In the development of database applications, often encounter problems of processing time, such as query the specified time records. Here are some of the common questions

, combined with some of their own experience, and you discuss this kind of problem.

Let's begin by introducing the use of several main functions of processing time in SQL Server:

GETDATE () function: Gets the current date and time of the system. Returns the value of the datetime type.

Usage: getdate ()

Example:

Select GETDATE () as Dte,dateadd (Day,-1,getdate ()) as Nowdat

Output results:

DTE Nowdat

1999-11-21 19:13:10.083 1999-11-20 19:13:10.083

(1 row (s) affected)

DatePart () Function: Returns the specified portion of a time as an integer.

Usage: DATEPART (datepart,date)

Parameter description: DatePart to return the part of the time, commonly used to value year, month, day, hour, minute.

Date is the specified time.

Example:

SELECT DATEPART (month, GETDATE ()) as ' month number '

Output results:

Month number

11

(1 row (s) affected)

DATEADD () Function: Returns a new time value by adding an integer value to the specified part of the specified time.

Usage: DATEADD (datepart,number,date)

Parameter description: DatePart (IBID.)

Date (IBID.)

Number to increase the value of the integer, can be positive negative, positive values return the time after the date value, negative returns date

Previous time value

Example:

Select GETDATE () as Today

Select DATEADD (Day,-1,getdate ())

Select DATEADD (Day,1,getdate ())

Output:

Today

1999-11-21 19:42:41.410

(1 row (s) affected)

Yesterday

1999-11-20 19:42:41.410

(1 row (s) affected)

Tomorrow

1999-11-22 19:42:41.410

(1 row (s) affected)

DateDiff () Function: Returns the difference that is calculated in the specified time portion of two time. Returns an integer value. such as 1991-6-12 and 1991-6-21

Between the day

To calculate the difference between 9 days, 1998-6-12 and 1999-6-23 by year, the difference is 1 years, 1999-12-1 and 1999-3-12 monthly difference of 9 months

Usage: DateDiff (DAREPART,DATE1,DATE2)

Parameter description: DatePart (IBID.)

Date1, Date2 (ditto date)

Example:

Select DateDiff (Month, ' 1991-6-12 ', ' 1992-6-21 ') as a

SQL Date Formatting

0 or 100 (*) default value mon dd yyyy hh:miam (or PM)


1 101 American Mm/dd/yyyy


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 + millisecond Mon dd yyyy hh:mi:ss:mmmAM (or PM)


10 110 American Mm-dd-yy


11 111 Japanese Yy/mm/dd


112 ISO YYMMDD


-13 or 113 (*) Euro defaults + millisecond dd Mon yyyy hh:mm:ss:mmm (24h)


114-hh:mi:ss:mmm (24h)


-20 or (*) ODBC specification YYYY-MM-DD HH:MM:SS[.FFF]


-21 or 121 (*) ODBC specification (with milliseconds) Yyyy-mm-dd HH:MM:SS[.FFF]


-126 (* * *) ISO8601 yyyy-mm-dd Thh:mm:ss:mmm (without spaces)


-130* Kuwait DD Mon yyyy Hh:mi:ss:mmmAM


-131* Kuwaiti Dd/mm/yy Hh:mi:ss:mmmAM

A very powerful date format function in SQL Server


Select CONVERT (varchar), GETDATE (), 0): 2006 10:57am


Select CONVERT (varchar), GETDATE (), 1): 05/16/06


Select CONVERT (varchar), GETDATE (), 2): 06.05.16


Select CONVERT (varchar), GETDATE (), 3): 16/05/06


Select CONVERT (varchar), GETDATE (), 4): 16.05.06


Select CONVERT (varchar), GETDATE (), 5): 16-05-06


Select CONVERT (varchar), GETDATE (), 6): 16 05 06


Select CONVERT (varchar), GETDATE (), 7:05 16, 06


Select CONVERT (varchar), GETDATE (), 8): 10:57:46


Select CONVERT (varchar), GETDATE (), 9): 2006 10:57:46:827am


Select CONVERT (varchar), GETDATE (), 10): 05-16-06


Select CONVERT (varchar), GETDATE (), 11): 06/05/16


Select CONVERT (varchar), GETDATE (), 12): 060516


Select CONVERT (varchar), GETDATE (), 13:16 05 2006 10:57:46:937


Select CONVERT (varchar), GETDATE (), 14): 10:57:46:967


Select CONVERT (varchar), GETDATE (), 20): 2006-05-16 10:57:47


Select CONVERT (varchar), GETDATE (), 21): 2006-05-16 10:57:47.157


Select CONVERT (varchar (MB), GETDATE (): 05/16/06 10:57:47 AM


Select CONVERT (varchar), GETDATE (), 23): 2006-05-16


Select CONVERT (varchar), GETDATE (), 24): 10:57:47


Select CONVERT (varchar), GETDATE (), 25): 2006-05-16 10:57:47.250


Select CONVERT (varchar), GETDATE (): 2006 10:57am


Select CONVERT (varchar), GETDATE (), 101): 05/16/2006


Select CONVERT (varchar), GETDATE (), 102): 2006.05.16


Select CONVERT (varchar), GETDATE (), 103): 16/05/2006


Select CONVERT (varchar), GETDATE (), 104): 16.05.2006


Select CONVERT (varchar), GETDATE (), 105): 16-05-2006


Select CONVERT (varchar), GETDATE (), 106): 16 05 2006


Select CONVERT (varchar), GETDATE (), 107:05 16, 2006


Select CONVERT (varchar), GETDATE (), 108): 10:57:49


Select CONVERT (varchar), GETDATE (), 109): 2006 10:57:49:437am


Select CONVERT (varchar), GETDATE (), 110): 05-16-2006


Select CONVERT (varchar), GETDATE (), 111): 2006/05/16


Select CONVERT (varchar), GETDATE (), 112): 20060516


Select CONVERT (varchar), GETDATE (), 113:16 05 2006 10:57:49:513


Select CONVERT (varchar), GETDATE (), 114): 10:57:49:547


Select CONVERT (varchar), GETDATE (), 120): 2006-05-16 10:57:49


Select CONVERT (varchar), GETDATE (), 121): 2006-05-16 10:57:49.700


Select CONVERT (varchar), GETDATE (), 126): 2006-05-16t10:57:49.827


Select CONVERT (varchar), GETDATE (), 130):???? ?????? 1427 10:57:49:907am


Select CONVERT (varchar), GETDATE (), 131): 18/04/1427 10:57:49:920am

Common:
Select CONVERT (varchar), GETDATE (), 8): 10:57:46
Select CONVERT (varchar), GETDATE (), 24): 10:57:47
Select CONVERT (varchar), GETDATE (), 108): 10:57:49
Select CONVERT (varchar), GETDATE (), 12): 060516
Select CONVERT (varchar), GETDATE (), 23): 2006-05-16

Usage of convert conversion function in SQL

How to use convert:

////////////////////////////////////////////////////////////////////////////////////////

Format:
CONVERT (Data_type,expression[,style])

Description
This style is generally in the type of time (datetime,smalldatetime) and String type (Nchar,nvarchar,char,varchar)
When they are converted to each other.

Example:
Select CONVERT (varchar), GETDATE (), () now
Result is
Now
---------------------------------------
09/15/2001

/////////////////////////////////////////////////////////////////////////////////////

The style number has the following meanings when converting time

-------------------------------------------------------------------------------------------------


Style (2-bit year) | Style (4-bit year) | Input and output formats


-------------------------------------------------------------------------------------------------


- | 0 or 100 | Mon dd yyyy hh:miam (or PM)


-------------------------------------------------------------------------------------------------


1 | 101 | Mm/dd/yy


-------------------------------------------------------------------------------------------------


2 | 102 | Yy-mm-dd


-------------------------------------------------------------------------------------------------


3 | 103 | Dd/mm/yy


-------------------------------------------------------------------------------------------------


4 | 104 | Dd-mm-yy


-------------------------------------------------------------------------------------------------


5 | 105 | Dd-mm-yy


-------------------------------------------------------------------------------------------------


6 | 106 | DD Mon yy


-------------------------------------------------------------------------------------------------


7 | 107 | Mon dd,yy


-------------------------------------------------------------------------------------------------


8 | 108 | Hh:mm:ss


-------------------------------------------------------------------------------------------------


- | 9 or 109 | Mon dd yyyy Hh:mi:ss:mmmmAM (or PM)


-------------------------------------------------------------------------------------------------


10 | 110 | Mm-dd-yy


-------------------------------------------------------------------------------------------------


11 | 111 | Yy/mm/dd


-------------------------------------------------------------------------------------------------


12 | 112 | Yymmdd


-------------------------------------------------------------------------------------------------


- | or 113 | DD Mon yyyy hh:mi:ss:mmm (24-hour system)


-------------------------------------------------------------------------------------------------


14 | 114 | Hh:mi:ss:mmm (24-hour system)


-------------------------------------------------------------------------------------------------


- | or 120 | Yyyy-mm-dd Hh:mi:ss (24-hour system)


-------------------------------------------------------------------------------------------------


- | or 121 | Yyyy-mm-dd Hh:mi:ss:mmm (24-hour system)

Date Convert convert function

SELECT CONVERT (VARCHAR (), GETDATE (), 111)


In this example, the function convert () converts the date format to show the 1997/11/30


To get a different format date and time, you need to use the function convert (). For example, when the following statement executes, the time displayed will include milliseconds:


SELECT CONVERT (VARCHAR (), GETDATE (), 9)


Note the use of the number 9 in the example. This number indicates which date and time format to use when displaying the date and time. When this statement is executed, the following date and time are displayed:


Simplified Chinese os:02 6 2007 4:33:56:530pm


English Os:nov 1997 3:29:55:170am


The style parameter in the CONVERT function provides a variety of date display formats for datetime or smalldatetime conversions to char or varchar. The numeric parameter of style determines how the date is displayed. The year can be displayed as two digits (yy), or it can be displayed as a 4-digit number (yyyy). For example, assigning a style parameter to 100 will give you a 4-bit age, which includes the century value (yyyy).


The following table lists the available values for the style parameter and the various date formats that you can use. When you use the style parameter with the smalldatetime type, those formats that include seconds and milliseconds will display zeros at their bit values.


Type value standard output


0 Default Mon dd yyyy hh:miam


1 USA mm/dd/yy


2 ANSI yy.mm.dd


3 British/french Dd/mm/yy


4 German dd.mm.yy


5 Italian Dd-mm-yy


6-dd Mon yy


7-mon Dd,yy


8-hh:mi:ss


9 Default + millisecondsr11;mon dd yyyy hh:mi:ss:mmmAM (OR)


USA Mm-dd-yy


JAPAN YY/MM/DD


ISO YYMMDD


Europe Default + millisecondsr11;dd mon yyyy hh:mi:ss:mmm (24h)


14-hh:mi:ss:mmm (24h)


The type value is preceded by 1, for example 101 shows MM/DD/YYYY

---------------------------------------------------------------------------------
Sometimes in some fields of the database due to the special consideration of the relationship
These fields are stored in char or nchar format for dates

If you want to transfer these fields to a consistent date format (such as YYYY/MM/DD)
For example, 1988/1/1 turns into 1988/01/01.
Please try to refer to the following syntax ...

SELECT CONVERT (varchar), convert (datetime,employee_birthday,111), 111)
From employee

Examples of other format conversions are as follows:
--yyyy/mm/dd
Select Convert (varchar), Getdate (), 111)
--yyyymmdd
Select Convert (varchar), Getdate (), 112)
--hh:mm:ss
Select Convert (varchar (8), Getdate (), 108)
--hh:mm:ss:mmm
Select Convert (varchar (), Getdate (), 114)

---------------------------------------------------------------------------------
How do I get only the current date of the database in SQL language, and the format is "YYYY-MM-DD"?
Select CONVERT (char (), GETDATE (), 120)

---------------------------------------------------------------------------------

--use of style when converting characters to dates

--1. When style=101, the date string is: mm/dd/yyyy format
SELECT CONVERT (datetime, ' 11/1/2003 ', 101)
--Results: 2003-11-01 00:00:00.000

--2. When style=101, the date string is: dd/mm/yyyy format
SELECT CONVERT (datetime, ' 11/1/2003 ', 103)
--Results: 2003-01-11 00:00:00.000

/*== Date converted to string ==*/
DECLARE @dt datetime
SET @dt = ' 2003-1-11 '

--1. When style=101, it means converting the date to: mm/dd/yyyy format
SELECT CONVERT (varchar, @dt, 101)
-Results: 01/11/2003

--2. When style=103, it means converting the date to: dd/mm/yyyy format
SELECT CONVERT (varchar, @dt, 103)
-Results: 11/01/2003

/*== This is a common mistake many people make, a style style that uses dates for non-date conversions ==*/
SELECT CONVERT (varchar, ' 2003-1-11 ', 101)
-Results: 2003-1-11

--1.
/*--description
Set DateFormat settings also affect the processing of converting character dates to dates using convert
But does not affect the explicitly specified style of convert processing.
--*/

--example, in the following example, the first convert transformation does not specify a style, the result of the transformation is affected by the set DataFormat, and the second convert transformation specifies style, which is affected by style.
--Set input date order to day/month/year
SET DateFormat dmy

--the convert conversion without specifying a style parameter will be affected by the set DateFormat
SELECT CONVERT (datetime, ' 2-1-2005 ')
--Results: 2005-01-02 00:00:00.000

--Specifies that the convert conversion of the style parameter is not affected by the set DateFormat
SELECT CONVERT (datetime, ' 2-1-2005 ', 101)
--Results: 2005-02-01 00:00:00.000
Go

--2.
/*--description

If you enter a date that contains the century section, the date is interpreted
The interpretation of the year is not affected by set DateFormat settings.
--*/

--for example, in the following code, the same set DateFormat setting, where the century portion of the date is entered and the century part of the date not entered, is interpreted with different date results.
DECLARE @dt datetime

--setting set DateFormat as: Year of month
SET DateFormat MDY

--The specified century part of the date entered
SET @dt = ' 01-2002-03 '
SELECT @dt
--Results: 2002-01-03 00:00:00.000

--Don't specify the century part in the date entered
SET @dt = ' 01-02-03 '
SELECT @dt
--Results: 2003-01-02 00:00:00.000
Go

--3.
/*--description

If the date entered does not contain a date separator, then SQL Server interprets the date
Setting of set DATEFORMAT is ignored.
--*/

--for example, in the following code, the character date without the date separator is the same as the result of the different set DateFormat settings.
DECLARE @dt datetime

--setting set DateFormat as: Year of month
SET DateFormat MDY
SET @dt = ' 010203 '
SELECT @dt
--Results: 2001-02-03 00:00:00.000

--setting set DateFormat as: Sun Moon Year
SET DateFormat dmy
SET @dt = ' 010203 '
SELECT @dt
--Results: 2001-02-03 00:00:00.000

--date separator included in the date entered
SET @dt = ' 01-02-03 '
SELECT @dt
--Results: 2003-02-01 00:00:00.000

-The following example shows the different results of the datename and convert functions under different locales (SET LANGUAGE).
Use master

--Setting the session's locale as: 中文版
SET LANGUAGE N ' 中文版 '
SELECT
Datename (Month,getdate ()) as [Month],
Datename (Weekday,getdate ()) as [weekday],
Convert (Varchar,getdate (), 109) as [convert]
/*--results:
Month Weekday CONVERT
------------- -------------- -------------------------------
March Tuesday Mar 8:59pm
--*/

--Set the locale of the session to: Simplified Chinese
SET LANGUAGE N ' Simplified Chinese '
SELECT
Datename (Month,getdate ()) as [Month],
Datename (Weekday,getdate ()) as [weekday],
Convert (Varchar,getdate (), 109) as [convert]
/*--Results
Month Weekday CONVERT
------------- --------------- -----------------------------------------
05 Weeks 405 2:49:20:607PM