SQL CONVERT function Use summary _mssql

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

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.

Statement results


SELECT CONVERT (varchar), GETDATE (), 0) 2009 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) 2009 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 (), 2009 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) 2009 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 (), 130)??? 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 year) |     Style (4-bit year) | Input and output formats


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


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


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


1 |     101 The USA | Mm/dd/yy


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


2 |     102 ANSI | Yy-mm-dd


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


3 |     103 British Law | 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 the USA | Mm-dd-yy


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


11 |     111 Japan | Yy/mm/dd


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


12 |     112 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
Use CONVERT:

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

Parameters

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 user-defined data types.
Length

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

Style

Date format style, whereby DateTime or smalldatetime data is converted to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or string format styles, whereby float, Real, money, or smallmoney data is converted to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).

SQL Server supports the data format in the Arabic style using the Kuwaiti algorithm.

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

no century Digit (yy)	band century Digit (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	United Kingdom/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 defaults + 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 (excluding spaces)
-	130*	hijri****	DD Mon yyyy hh:mi:ss:mmmAM
-	131*	hijri****	Dd/mm/yy Hh:mi:ss:mmmAM

* Default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, 21, or 121) always return century digits (yyyy).
* * input when converting to datetime, 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 variations, 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, two-digit year 49 is interpreted as 2049, and two-digit year 50 is interpreted as 1950. Many client applications, such as those that are based on OLE automation objects, use 2030 as the cutoff year. SQL Server provides a configuration option ("Cutoff year for two digits") to change the cutoff year used by SQL Server and to treat dates consistently. However, the safest way to do this is to specify a four-digit year.

When you convert from smalldatetime to character data, a style that contains seconds or milliseconds will display zeros in those positions. When converting from datetime or smalldatetime values, you can truncate unwanted date parts by using the appropriate char or varchar data type length.

PS: Combining the DateDiff () function is especially useful for comparing time.

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

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

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 in 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 abbreviation (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-millisecond 0 ~ 999

Access and ASP Use Date () and now () to obtain the system datetime, where Datediff,dateadd,datepart is also available for access and ASP, and these functions are similar in usage

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 SQL Server DATEPART () function returns part of SQL Server datetime field.

The syntax for the SQL Server DATEPART () function is:
DATEPART (Portion, datetime)
where datetime is a SQL Server datetime field and part of the name is one of the following: Ms for milliseconds
Yy for year
Qq for quarter of the year
Mm for Month
Dy for the "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 judge the first or last day of one months. Most of you probably know how to split dates (year, month, day, etc.), and then just use the split year, month, day, etc in several functions to calculate the date you need! In this article, I'll show you how to use the DateAdd and DATEDIFF functions to calculate 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 the one week 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 established in Sunday as the first day of the week, with the first day set to 7. If your first day setting is not the same, you may need to adjust these examples so that it matches the different first-day settings. You can check the first day setting by using the @ @DATEFIRST function.

To understand these examples, let's review the DateDiff and DATEADD functions first. The DateDiff function calculates the total number of hours, days, weeks, months, and years of time between two dates. The DateAdd function calculates a date by adding and reducing time intervals to obtain a new date. Learn more about DateDiff and DATEADD functions and time intervals to read Microsoft online Help.

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

The first day of one months

For the first example, I will show you how to go from the current date to the last day of the month. Please 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 will get the date you want to calculate by calculating the previous time interval and then adding and reducing it.

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

Let's take this statement apart to see how it works. The most central function is getdate (), which most people know is the function that returns the current date and time. The next executing function DATEDIFF (Mm,0,getdate ()) is the number of months between the current date and the date "1900-01-01 00:00:00.000". Keep in mind: time and time variables, like milliseconds, are calculated from "1900-01-01 00:00:00.000". That's why you can specify the first time expression in the DateDiff function to be "0". 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 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 also uses this technique to produce a different date from the current date.

Monday of the Week

Here I use the WK time interval to calculate which day is this week's Monday.

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

The first day of the year

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

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 what to do.

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

The middle of the day

The GETDATE () function was used to truncate the time section to return the time value, taking 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.

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

Deep DateDiff and DateAdd function calculation

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

All the examples so far are just calculating the current time and the number of time intervals between "1900-01-01" and then adding it to the "1900-01-01" time interval to calculate the date. Suppose you modify the number of time intervals, or use different time intervals to invoke the DateAdd function, or subtract the time interval instead of increasing, then you can find and how many different dates through these small adjustments.

Here are four examples that use another DateAdd function to calculate the last day to replace the DATEADD function two intervals before and after each other.

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 the example on the last day of one months. One thing to keep in mind is that the time in SQL Server is accurate to 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 that a SQL Server can record the last moment of the day ("23:59:59:997").

Last day of last year

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

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

The 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. Modifications need to be added 1 to the time interval between the current date and the "1900-01-01" comparison with DateDiff. By adding 1 months, I figure out the first day of the next month, then subtract 3 milliseconds, so I can figure out the last day of the month. 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 this month

Well, now is the last example. Here I want 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)