Shulou(Shulou.com)05/31 Report--

How to use the convert function in sql? for this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

CONVERT (data_type,expression [, style]) convert (varchar (10), field name, translation format)

Note: this style is generally used when converting between time type (datetime,smalldatetime) and string type (nchar,nvarchar,char,varchar).

Sentence result SELECT CONVERT (varchar, GETDATE (), 0) 07 15 2009 4:06PMSELECT CONVERT (varchar, GETDATE (), 1) 07/15/09SELECT CONVERT (varchar, GETDATE (), 2) 09.07.15SELECT CONVERT (varchar, GETDATE (), 3) 15/07/09SELECT CONVERT (varchar, GETDATE (), 4) 15.07.09SELECT CONVERT (varchar, GETDATE, 5) 15-07-09SELECT CONVERT (varchar (100), GETDATE () 6) 15 07 09SELECT CONVERT (varchar, GETDATE (), 7) 0715, 09SELECT CONVERT (varchar (100), GETDATE (), 8) 16:06:26SELECT CONVERT (varchar, GETDATE (), 9) 0715 2009 4:06:26:513PMSELECT CONVERT (varchar, GETDATE (), 10) 07-15-09SELECT CONVERT (varchar, GETDATE, 11) 09/07/15SELECT CONVERT (varchar, GETDATE (), 12) 090715SELECT CONVERT (varchar 13) 15 07 2009 16:06:26:513SELECT CONVERT (varchar, GETDATE (), 14) 16:06:26:513SELECT CONVERT (varchar, GETDATE (), 20) 2009-07-15 16:06:26SELECT CONVERT (varchar, GETDATE (), 21) 2009-07-15 16:06:26.513SELECT CONVERT (varchar, GETDATE (), 22) 07 Legend 4:06:26 PMSELECT CONVERT (varchar, GETDATE (), 23) 2009-07-15SELECT CONVERT (varchar GETDATE (), 24) 16:06:26SELECT CONVERT (varchar, GETDATE (), 25) 2009-07-15 16:06:26.513SELECT CONVERT (varchar, GETDATE (), 100) 07 15 2009 4:06PMSELECT CONVERT (varchar, GETDATE (), 1010) 07/15/2009SELECT CONVERT (varchar, GETDATE) 2009.07.15SELECT CONVERT (varchar, GETDATE (), 15/07/2009SELECT CONVERT (varchar, GETDATE) 15.07.2009SELECT CONVERT (varchar, GETDATE) 15-07-2009SELECT CONVERT (varchar, GETDATE) 15 07 2009SELECT CONVERT (varchar, GETDATE, 107) 07 15, 2009SELECT CONVERT (varchar, GETDATE, 108) 16:06:26SELECT CONVERT (varchar, GETDATE, 10 9) 07 15 2009 4:06:26:513PMSELECT CONVERT (varchar, GETDATE, 10) 07-15-2009SELECT CONVERT (varchar (100), GETDATE () 2009/07/15SELECT CONVERT (varchar, GETDATE), 20090715SELECT CONVERT (varchar, GETDATE,) 15 07 2009 16:06:26:513SELECT CONVERT (varchar, GETDATE,) 16:06:26:513SELECT CONVERT (varchar, GETDATE, 120) 2009-07-15 16:06:26SELECT CONVERT (varchar, GETDATE,) 2009-07-15 16:06:26.513SELECT CONVERT (varchar, GETDATE) 2009-07-15T16:06:26.513SELECT CONVERT (varchar (100,130) 23? 1430 4:06:26:513PMSELECT CONVERT (varchar (100,131) GETDATE (), 131b) 23 15T16:06:26.513SELECT CONVERT 07max 1430 4:06:26:513PM

The meaning of style numbers when converting time is as follows:

-Style (2 digits represent year) | Style ( 4 bits represent year) | input / output format- -- 0 | 100 | mon dd yyyy hh:miAM (or PM)- -1 | 101US | mm/dd/yy- -2 | 102 ANSI | yy-mm-dd- -3 | 103UK / 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 | 109 | mon dd yyyy hh:mi:ss:mmmmAM (or PM)-10 | 110US | mm- Dd-yy-11 | 111Japan | yy/mm / dd-12 | 112 ISO | yymmdd -13 | 113 European default value | dd mon yyyy hh:mi: Ss:mmm (24-hour system)-14 | 114 | | hh:mi:ss:mmm (24-hour system)- -- 20 | 120 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. User-defined data types cannot be used. Length

Optional parameters of the nchar, nvarchar, char, varchar, binary, or varbinary data type.

Style

Date format style to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or string format styles to convert float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).

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

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

-0 or 100 (*) default mon dd yyyy hh:miAM (or PM) 1101 US mm/dd/yyyy2102ANSIyy.mm.dd3103 UK / France dd/mm/yy4104 Germany dd.mm.yy5105 Italian dd-mm-yy6106-dd mon yy7107-mon dd Yy8108-hh:mm:ss-9 or 109 (*) default + millisecond mon dd yyyy hh:mi:ss:mmmAM (or PM) 10110 US mm-dd-yy11111 Japan yy/mm/dd12112ISOyymmdd-13 or 113 (*) European default + millisecond dd mon yyyy hh:mm:ss:mmm (24 h) 14114-hh:mi:ss:mmm (24 h)-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 (* *) ISO8601yyyy-mm-dd Thh:mm:ss.mmm (without spaces)-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 converted to datetime; output when converted to character data. * is dedicated to XML. For conversion from datetime or smalldatetime to character data, the output format is shown in the table. For conversion from float, money, or smallmoney to character data, the output is equivalent to style 2. For the conversion from real to character data, the output is equivalent to style 1. * Hijri is a calendar system with several variations, and Microsoft ®SQL Server ™2000 uses the Kuwait algorithm.

Important by default, SQL Server interprets a two-digit year based on the cut-off year 2049. That is, a two-digit year 49 is interpreted as 2049, while a two-digit year 50 is interpreted as 1950. Many client applications, such as those based on OLE automation objects, use 2030 as the cut-off year. SQL Server provides a configuration option ("two-digit cutoff year") to change the cutoff year used by SQL Server and to handle date consistency. The safest way, however, is to specify a four-digit year.

When converting from smalldatetime to character data, styles that contain seconds or milliseconds display zeros at these locations. When converting from datetime or smalldatetime values, you can truncate unwanted date parts by using the appropriate char or varchar data type length.

PS: it is especially useful to compare time with the datediff () function.

- -

1. Current system date and 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 select dateadd (day,2,'2004-10-15') to the date-return: 2004-10-17 00 select dateadd 00.000

3. Datediff returns the number of date and time boundaries across two specified dates. Select datediff (day,'2004-09-01)-return: 17

4. Datepart returns an integer representing the specified date portion of the specified date. SELECT DATEPART (month, '2004-10-15')-returns 10

5. Datename returns the string SELECT datename (weekday, '2004-10-15') representing the specified date portion of the specified date-return: Friday

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

Select current date = convert (varchar (10), getdate (), 120), current time = convert (varchar (8), getdate (), 114)

Select datename (dw,'2004-10-15')

Select week of this year = datename (week,'2004-10-15'), today is the day of the week = datename (weekday,'2004-10-15')

Function parameter / function GetDate () returns the current date and time of the system DateDiff (interval,date1,date2) returns the difference between date2 and date1 in the way specified by interval date2-date1 DateAdd (interval,number,date) in the way specified by interval, plus the date after number DatePart (interval,date) returns the date date, the integer value DateName (interval,date) corresponding to the specified part of interval is returned to the date date The string name corresponding to the specified part of the interval

The setting value of parameter interval is as follows:

Value abbreviations (Sql Server) Access and ASP indicate the number of days in a year from 1 to 4 Month Mm m months from 1753 to 9999 Quarter Qq q, the days of a week from 1 to 31 Weekday Dw w, the days of a week from 1 to 7 Week Wk ww, the week of the week from 0 to 51 Hour Hh h, 0 to 23 Minute Min minutes, 0 to 59 Millisecond Ms seconds, and 0 to 999 milliseconds.

Date () and now () are used in access and asp to get the system date and time; DateDiff,DateAdd,DatePart can also be used in Access and asp, and the usage of these functions is similar

For example: 1.GetDate () is used for sql server: select GetDate ()

The return value of 2.DateDiff is 514592 seconds (DateDiff). The return value is 5 days.

The return value of 3.DatePart is 2, that is, Monday (1 on Sunday, 7 on Saturday) DatePart ("daddy journal" on Monday, 1 on Sunday and 7 on Saturday). The return value of DatePart is 25, that is, the 25th DatePart.) the return value of DatePart on the 206th day of the year is 2005, that is, 2005.

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

The syntax of the SQLServer DATEPART () function is: DATEPART (portion, datetime), where datetime is the name of the SQLServer datetime field and part is one of the following: Ms for MillisecondsYy for YearQq for Quarter of the YearMm for MonthDy for the Day of the YearDd for Day of the MonthWk for WeekDw for the Day of the WeekHh for HourMi for MinuteSs for Second

Detailed instructions:

Usually, you need to get the current date and calculate some other dates. For example, your program may need to determine the first or last day of the month. Most of you probably know how to divide the date (year, month, day, etc.), and then just use the split 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 calculate some of the different dates you might use in your program. Before using the examples in this article, you must pay attention to the following problems. Most of the examples may not have the same results on different machines, depending on which day is the first day of the week. The DATEFIRST setting determines which day your system uses as the first day of the week. All the following examples are created with Sunday as the first day of the week, that is, the first day is set to 7. If your first day settings are different, you may need to adjust these examples to match the different first day settings. You can check the first day settings through 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 to get a new date by adding or subtracting the interval. To learn more about DATEDIFF and DATEADD functions and intervals, you can read the Microsoft online help.

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

The first day of the month

For the first example, I will show you how to get to the last day of the month from the current date. Please note that this and other examples in this article will only use the DATEDIFF and DATEADD functions to calculate the dates we want. Each example will calculate the desired date by calculating the previous time interval, and then adding and subtracting.

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

Let's separate this 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 function to be executed, DATEDIFF (mm,0,getdate ()), is to calculate the number of months between the current date and the date "1900-01-01 00 00.000". Remember: the period and time variables, like milliseconds, are calculated starting with "1900-01-01 00-00.000". This is 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 increasing 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 "000.000".

The trick of this calculation is to first calculate the interval between the current date and "1900-01-01", and then add it to "1900-01-01" to get a special date. This technique can be used to calculate many different dates. The next example also uses this technique to generate different dates from the current date.

Monday of this week

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

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

The first day of the year

Now use the interval of the year (yy) to show the first day of the year.

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

The 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)

In the middle of the night

You used to need to truncate the time part through the getdate () function 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 the midnight time.

SELECT DATEADD (dd, DATEDIFF (dd,0,getdate ()), 0)

In-depth calculation of DATEDIFF and DATEADD functions

As you can see, by using the simple DATEDIFF and DATEADD functions to calculate, you can find many different dates that may be meaningful.

All the examples so far just calculate the number of intervals between the current time and "1900-01-01", and then add it to the "1900-01-01" interval to calculate the date. Suppose you change the number of intervals, or use different intervals to call the DATEADD function, or subtract the intervals instead of increasing them, then you can find and many different dates through these small adjustments.

Here are four examples that use another DATEADD function to calculate the time interval before and after the last day to replace the DATEADD function.

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 the last day of the month. One thing to keep in mind is that in Sql Server the time 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 of the last moment of the day that Sql Server can record.

The last day of last year

To connect to the above example, to get the last day of last year, you need to subtract 3 milliseconds from the first day of this 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 this month, I need to modify the statement to get the last day of last month slightly. The modification requires adding 1 to the current date compared with the time interval returned by "1900-01-01" with DATEDIFF. By adding one month, I calculate the first day of the next month, and then subtract 3 milliseconds, thus calculating 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 (mmem0je getdate ()) + 1,0)

The last day of the year

You should master this practice now. This is the last day script of the year.

SELECT dateadd (ms,-3,DATEADD (yy, DATEDIFF (yy,0,getdate ()) + 1,0))

This is the answer to the question about how to use the convert function in sql. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.