SQL Server Date Conversion Daquan

Reproduced in this blog, the original blog: http://blog.csdn.net/baiduandxunlei/article/details/9180075

CONVERT (Data_type,expression[,style])
CONVERT (varchar (10), field name, conversion format)

Description
This style is typically in the time type (Datetime,smalldatetime) and the string type (Nchar,nvarchar,char,varchar)
It is only used when converting to each other.

Statement result
SELECT CONVERT (varchar), GETDATE (), 0) 4:06pm
SELECT CONVERT (varchar), GETDATE (), 1) 07/15/09
SELECT CONVERT (varchar), GETDATE (), 2) 09.07.15
SELECT CONVERT (varchar), GETDATE (), 3) 15/07/09
SELECT CONVERT (varchar), GETDATE (), 4) 15.07.09
SELECT CONVERT (varchar), GETDATE (), 5) 15-07-09
SELECT CONVERT (varchar), GETDATE (), 6) 15 07 09
SELECT CONVERT (varchar), GETDATE (), 7) 07 15, 09
SELECT CONVERT (varchar), GETDATE (), 8) 16:06:26
SELECT CONVERT (varchar), GETDATE (), 9) 4:06:26:513pm
SELECT CONVERT (varchar), GETDATE (), 10) 07-15-09
SELECT CONVERT (varchar), GETDATE (), 11) 09/07/15
SELECT CONVERT (varchar), GETDATE (), 12) 090715
SELECT CONVERT (varchar), GETDATE (), 13) 15 07 2009 16:06:26:513
SELECT CONVERT (varchar), GETDATE (), 14) 16:06:26:513
SELECT CONVERT (varchar), GETDATE (), 20) 2009-07-15 16:06:26
SELECT CONVERT (varchar), GETDATE (), 21) 2009-07-15 16:06:26.513
SELECT CONVERT (varchar), GETDATE (), 07/15/09 4:06:26 PM
SELECT CONVERT (varchar), GETDATE (), 23) 2009-07-15
SELECT CONVERT (varchar), GETDATE (), 24) 16:06:26
SELECT CONVERT (varchar), GETDATE (), 25) 2009-07-15 16:06:26.513
SELECT CONVERT (varchar), GETDATE (), (+)-4:06pm
SELECT CONVERT (varchar), GETDATE (), 101) 07/15/2009
SELECT CONVERT (varchar), GETDATE (), 102) 2009.07.15
SELECT CONVERT (varchar), GETDATE (), 103) 15/07/2009
SELECT CONVERT (varchar), GETDATE (), 104) 15.07.2009
SELECT CONVERT (varchar), GETDATE (), 105) 15-07-2009
SELECT CONVERT (varchar), GETDATE (), 106) 15 07 2009
SELECT CONVERT (varchar), GETDATE (), 107) 07 15, 2009
SELECT CONVERT (varchar), GETDATE (), 108) 16:06:26
SELECT CONVERT (varchar), GETDATE (), 109) from 4:06:26:513pm
SELECT CONVERT (varchar), GETDATE (), 110) 07-15-2009
SELECT CONVERT (varchar), GETDATE (), 111) 2009/07/15
SELECT CONVERT (varchar), GETDATE (), 112) 20090715
SELECT CONVERT (varchar), GETDATE (), 113) 15 07 2009 16:06:26:513
SELECT CONVERT (varchar), GETDATE (), 114) 16:06:26:513
SELECT CONVERT (varchar), GETDATE (), 120) 2009-07-15 16:06:26
SELECT CONVERT (varchar), GETDATE (), 121) 2009-07-15 16:06:26.513
SELECT CONVERT (varchar), GETDATE (), 126) 2009-07-15t16:06:26.513
SELECT CONVERT (varchar), GETDATE (), ()??? 1430 4:06:26:513PM
SELECT CONVERT (varchar), GETDATE (), 131) 23/07/1430 4:06:26:513pm

The style number has the following meanings when converting time:

------------------------------------------------------------------------------------------------------------
Style (2-bit for year) | Style (4-bit for year) | Input/output format
------------------------------------------------------------------------------------------------------------
0 | 100 | Mon dd yyyy hh:miam (or PM)
------------------------------------------------------------------------------------------------------------
1 | 101 USA | Mm/dd/yy
------------------------------------------------------------------------------------------------------------
2 | 102 ANSI | Yy-mm-dd
------------------------------------------------------------------------------------------------------------
3 | 103 Anglo-French | Dd/mm/yy
------------------------------------------------------------------------------------------------------------
4 | 104 Germany | Dd.mm.yy
------------------------------------------------------------------------------------------------------------
5 | 105 Italy | Dd-mm-yy
------------------------------------------------------------------------------------------------------------
6 | 106 | DD Mon yy
------------------------------------------------------------------------------------------------------------
7 | 107 | Mon dd,yy
------------------------------------------------------------------------------------------------------------
8 | 108 | Hh:mm:ss
------------------------------------------------------------------------------------------------------------
9 | 109 | Mon dd yyyy Hh:mi:ss:mmmmAM (or PM)
------------------------------------------------------------------------------------------------------------
10 | 110 USA | Mm-dd-yy
------------------------------------------------------------------------------------------------------------
11 | 111 Japan | Yy/mm/dd
------------------------------------------------------------------------------------------------------------
12 | ISO | Yymmdd
------------------------------------------------------------------------------------------------------------
13 | 113 Euro Default | DD Mon yyyy hh:mi:ss:mmm (24 hour system)
------------------------------------------------------------------------------------------------------------
14 | 114 | Hh:mi:ss:mmm (24-hour system)
------------------------------------------------------------------------------------------------------------
20 | ODBC Specification | Yyyy-mm-dd Hh:mi:ss (24-hour system)
------------------------------------------------------------------------------------------------------------
21 | 121 | Yyyy-mm-dd Hh:mi:ss:mmm (24-hour system)
------------------------------------------------------------------------------------------------------------

Description
Using CONVERT:

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

Expression

Is any valid microsoft®sql Server™ expression:

Data_type

The data types provided by the target system, including bigint and sql_variant. You cannot use a user-defined data type.
Length

Optional parameters for nchar, nvarchar, char, varchar, binary, or varbinary data types.

Style

A date format style whereby DateTime or smalldatetime data is converted to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data type) or a string format style that will float, Real, money, or smallmoney data is converted to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).

SQL Server supports data formats in Arabian styles using the Kuwaiti algorithm.

In the table, the two columns on the left represent the style values that convert datetime or smalldatetime to character data. Add 100 to the style value to get a four-bit year (yyyy) that includes century digits.

Without century digits (yy)
With century digits (yyyy)
Standard
Input/Output * *

-
0 or 100 (*)
Default value
Mon dd yyyy hh:miam (or PM)

1
101
United States
Mm/dd/yyyy

2
102
Ansi
Yy.mm.dd

3
103
UK/France
Dd/mm/yy

4
104
Germany
Dd.mm.yy

5
105
Italy
Dd-mm-yy

6
106
-
DD Mon yy

7
107
-
Mon dd, yy

8
108
-
Hh:mm:ss

-
9 or 109 (*)
Default value + milliseconds
Mon dd yyyy hh:mi:ss:mmmAM (or PM)

10
110
United States
Mm-dd-yy

11
111
Japan
Yy/mm/dd

12
112
Iso
Yymmdd

-
13 or 113 (*)
European default + milliseconds
DD Mon yyyy hh:mm:ss:mmm (24h)

14
114
-
Hh:mi:ss:mmm (24h)

-
20 or 120 (*)
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 (no spaces included)

-
130*
hijri****
DD Mon yyyy hh:mi:ss:mmmAM

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

* The default value (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, 21, or 121) always returns century digits (yyyy).
* * input when converting to datetime, and output when converting to character data.
specifically for XML. For conversions from DateTime or smalldatetime to character data, the output format is shown in the table. For conversions from float, money, or smallmoney to character data, the output is equivalent to style 2. For conversions from Real to character data, the output is equivalent to style 1.
Hijri is a calendar system with several forms of change, Microsoft®sql server™2000 uses the Kuwaiti algorithm.

Important by default, SQL Server interprets two-digit years based on the cutoff year 2049. That is, the two-digit year 49 is interpreted as 2049, and the two-digit year 50 is interpreted as 1950. Many client applications, such as those based on OLE automation objects, use 2030 as the cutoff year. SQL Server provides a configuration option ("Two-digit cutoff year") to change the cutoff year used by SQL Server and to make the dates consistent. However, the safest option is to specify a four-digit year.

When converting from smalldatetime to character data, styles containing seconds or milliseconds will display zeros at these locations. When converting from a datetime or smalldatetime value, you can truncate the unwanted date part by using the appropriate char or varchar data type length.

PS: Combining the DateDiff () function to compare time is particularly useful.

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

1. Current system date, time
Select GETDATE ()

2. DateAdd returns a new datetime value based on adding a period of time to the specified date
Example: Add 2 days to the date
Select DATEADD (day,2, ' 2004-10-15 ')--return: 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

4. DatePart returns an integer representing the specified date portion 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 a few weeks =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) with the date specified in interval, plus number
DatePart (interval,date) returns the integer value corresponding to the specified portion of the date, interval
Datename (interval,date) returns the name of the string that corresponds to the specified part of the date, interval

The setting values for the parameter interval are as follows:

Value abbreviations (SQL Server) Access and ASP descriptions
Year Yy yyyy 1753 ~ 9999
Quarter Qq Q Season 1 ~ 4
Month Mm M 1 ~ 12
Day of the year Dy y the number of days of the year, the day of the first 1-366
Day Dd D, 1-31
Weekday Dw W Day of the week, day of the week 1-7
Week Wk WW Week, week of the year 0 ~ 51
Hour Hh H 0 ~ 23
Minute Mi N min 0 ~ 59
Second Ss s seconds 0 ~ 59
Millisecond MS-MS 0 ~ 999

In Access and ASP, date () and now () are used to obtain system datetime, where Datediff,dateadd,datepart is also used in Access and ASP, and the usage 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 that is 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 that is 2005

The SQL Server DATEPART () function returns a portion of the SQL Server datetime field.

The syntax for the SQL Server DATEPART () function is:
DATEPART (Portion, datetime)
where datetime is the SQL Server datetime field and the name of the section is one of the following: Ms for Milliseconds
Yy for year
Qq for Quarter of the year
Mm for Month
Dy for the day of the year
Dd for day of the Month
Wk for Week
Dw for the day of the Week
Hh for Hour
Mi for Minute
Ss for Second

Detailed Description:

Usually, you need to get the current date and calculate some other date, for example, your program may need to determine the first or last day of one months. Most of you probably know how to divide the date (year, month, day, etc.), and then just use the divided year, month, day, etc. in a few functions to calculate the date you need! In this article, I'll show you how to use the DateAdd and DATEDIFF functions to figure out some of the different dates you might want to use in your program.
Before using the examples in this article, you must be aware of the following issues. Most probably not all examples of the results performed on different machines may not be the same, which is entirely determined by which day is the first day of one weeks of this setting. The first day (Datefirst) setting determines which day your system uses as the first day of the week. All of the following examples are created in Sunday as the first day of the week, that is, the first day is set to 7. If your first day is different, you may need to adjust these examples so that it matches the settings of the first day. You can check the first day setting by using the @ @DATEFIRST function.

To understand these examples, let's review the DateDiff and DATEADD functions. The DateDiff function calculates the total number of hours, days, weeks, months, years, and so on, between two dates. The DateAdd function calculates a date by adding or decreasing the time interval to obtain a new date. To learn more about the DateDiff and DATEADD functions and the time interval you can read Microsoft online Help.

Using the DateDiff and DATEADD functions to calculate dates is a little different from the way you would think to convert from the current date to the date you need. You have to think about it in terms of time interval. For example, how many intervals are there between the current date and the date you want to get, or how much time interval between today and a day (such as 1900-1-1), and so on. Understanding how to focus on time intervals can help you easily understand my different date calculation examples.

First day of one months

For the first example, I'll show you how to go to the last day of the month from the current date. Note: This example, and the other examples in this article, will only use the DateDiff and DATEADD functions to calculate the date we want. Each example is calculated by calculating the time interval, and then adding and subtraction to get the date you want to calculate.

This is the SQL script that calculates the first day of the one month:
SELECT DATEADD (mm, DATEDIFF (Mm,0,getdate ()), 0)

Let's separate the statement to see how it works. The core function is getdate (), which most people know is a function that returns the current date and time. The next executed function, DATEDIFF (Mm,0,getdate ()), is the number of months between the date of the current date and "1900-01-01 00:00:00.000". Remember: Time and time variables, like milliseconds, are calculated starting with "1900-01-01 00:00:00.000". That's why you can specify the first time expression as "0" in the DateDiff function. The next function is DATEADD, increasing the number of months from the current date to "1900-01-01". By adding the predefined date "1900-01-01" and the number of months of the current date, we can get the first day of the month. In addition, the time portion of the calculated date will be "00:00:00.000".

The trick is to calculate the time interval of the current date to "1900-01-01" and then add it to "1900-01-01" to get a special date, a technique that can be used to calculate many different dates. The next example is to use this technique to produce different dates from the current date.

Monday of the Week

Here I use the time interval of week (wk) to calculate which day is the Monday of this week.

SELECT DATEADD (wk, DATEDIFF (Wk,0,getdate ()), 0)

The first day of the year

The first day of the year is now displayed with a time interval of year (yy).

SELECT DATEADD (yy, DATEDIFF (Yy,0,getdate ()), 0)

First day of the quarter

If you want to calculate the first day of the quarter, this example tells you how to do it.

SELECT DATEADD (QQ, DATEDIFF (Qq,0,getdate ()), 0)

The night of the day

Once it was necessary to intercept the time portion by the GETDATE () function in order to return the time value, it would take into account whether the current date is in the middle of the night. If so, this example uses the DateDiff and DATEADD functions to get a midnight point in time.

SELECT DATEADD (DD, DATEDIFF (Dd,0,getdate ()), 0)

In-depth datediff and DateAdd function calculations

You can understand that by using simple DateDiff and DateAdd function calculations, you can find many different dates that may be meaningful.

All the examples so far just calculate the time interval between the current time and "1900-01-01" and then add it to the "1900-01-01" interval to calculate the date. Assuming you modify the number of intervals, or use different intervals to invoke the DateAdd function, or subtract the time interval instead of the increment, you can find and vary the dates by these small adjustments.

Here are four examples using another DateAdd function to calculate the last day before and after two time intervals to replace the DATEADD function respectively.

The last day of last month

This is an example of calculating the last day of last month. It is obtained by subtracting 3 milliseconds from this example on the last day of one months. One thing to keep in mind is that the time in SQL Server is exactly 3 milliseconds. That's why I need to subtract 3 milliseconds to get the date and time I want.

SELECT DateAdd (Ms,-3,dateadd (mm, DATEDIFF (Mm,0,getdate ()), 0))

The time portion of the calculated date contains the time at which SQL Server can record the last moment of the day ("23:59:59:997").

Last day of last year

To get to the last day of last year, you need to subtract 3 milliseconds from the first sky this year.

SELECT DateAdd (Ms,-3,dateadd (yy, DATEDIFF (Yy,0,getdate ()), 0))

Last day of the month

Now, in order to get the last day of the month, I need to revise a little bit to get the last day of last month's statement. The modification needs to be added 1 to the time interval returned by the DateDiff comparison between the current date and "1900-01-01". By adding 1 months, I calculated the first day of the next month and then subtracted 3 milliseconds, so that the last day of the month was calculated. This is the SQL script that calculates the last day of the month.

SELECT DateAdd (Ms,-3,dateadd (mm, DATEDIFF (M,0,getdate ()) +1, 0))

The last day of the year

You should now master this practice, which is to calculate the last day of the year script

SELECT DateAdd (Ms,-3,dateadd (yy, DATEDIFF (Yy,0,getdate ()) +1, 0))

The first Monday of the month

Well, now is the last example. Here I'm going to calculate the first Monday of this month, which is the computed script.

Select DATEADD (wk, DATEDIFF (Wk,0,dateadd (Dd,6-datepart (Day,getdate ()), GETDATE ()), 0)

SQL Server Date Conversion Daquan