Use of the time format conversion function convert () in SQL Server

Source: Internet
Author: User
Tags date1 getdate

CONVERT (varchar (10), field name, conversion format)

Convert is a date conversion function, which is typically used when the time type (Datetime,smalldatetime) and the string type (Nchar,nvarchar,char,varchar) are converted to each other. The 3 parameters of the function: The 1th argument is the converted large, the 2nd is the field or function of the converted date, and the 3rd is the converted format.

Specific examples:
SELECT CONVERT (varchar), GETDATE (), 0): 10:57am
SELECT CONVERT (varchar), GETDATE (), 1): 05/16/11

SELECT CONVERT (varchar), GETDATE (), 2): 11.05.16
SELECT CONVERT (varchar), GETDATE (), 3): 16/05/11

SELECT CONVERT (varchar), GETDATE (), 4): 16.05.11
SELECT CONVERT (varchar), GETDATE (), 5): 16-05-11
SELECT CONVERT (varchar), GETDATE (), 6): 16 05 11
SELECT CONVERT (varchar), GETDATE (), 7): 05 16, 11
SELECT CONVERT (varchar), GETDATE (), 8): 10:57:46
SELECT CONVERT (varchar), GETDATE (), 9): 10:57:46:827am
SELECT CONVERT (varchar), GETDATE (), 10): 05-16-11
SELECT CONVERT (varchar), GETDATE (), 11): 11/05/16
SELECT CONVERT (varchar), GETDATE (), 12): 110516
SELECT CONVERT (varchar), GETDATE (), 13): 16 05 2011 10:57:46:937
SELECT CONVERT (varchar), GETDATE (), 14): 10:57:46:967
SELECT CONVERT (varchar), GETDATE (), 20): 2011-05-16 10:57:47
SELECT CONVERT (varchar), GETDATE (), 21): 2011-05-16 10:57:47.157
SELECT CONVERT (varchar), GETDATE (): 05/16/11 10:57:47 AM
SELECT CONVERT (varchar), GETDATE (), 23): 2011-05-16
SELECT CONVERT (varchar), GETDATE (), 24): 10:57:47
SELECT CONVERT (varchar), GETDATE (), 25): 2011-05-16 10:57:47.250
SELECT CONVERT (varchar), GETDATE (), (+): 10:57am
SELECT CONVERT (varchar), GETDATE (), 101): 05/16/2011

SELECT CONVERT (varchar), GETDATE (), 102): 2011.05.16
SELECT CONVERT (varchar), GETDATE (), 103): 16/05/2011

Select CONVERT (varchar), GETDATE (), 104): 16.05.2011
SELECT CONVERT (varchar), GETDATE (), 105): 16-05-2011
Select CONVERT (varchar), GETDATE (), 106): (+)
Select CONVERT (varchar, GETDATE (), 107): In the
Select CONVERT (varchar), GETDATE (), 108): 10:57:49
SELECT CONVERT (varchar), GETDATE (), 109): 10:57:49:437am
SELECT CONVERT (varchar), GETDATE (), (): 05-16-2011

SELECT CONVERT (varchar), GETDATE (), 111): 2011/05/16
SELECT CONVERT (varchar), GETDATE (), 112): 20110516
SELECT CONVERT (varchar), GETDATE (), 113): 16 05 2011 10:57:49:513
SELECT CONVERT (varchar), GETDATE (), 114): 10:57:49:547
SELECT CONVERT (varchar), GETDATE (), 120): 2011-05-16 10:57:49
SELECT CONVERT (varchar), GETDATE (), 121): 2011-05-16 10:57:49.700
SELECT CONVERT (varchar), GETDATE (), 126): 2011-05-16t10:57:49.827
SELECT CONVERT (varchar), GETDATE (), $): 10:57:49:513am
SELECT CONVERT (varchar), GETDATE (), 131): 18/04/1427 10:57:49:920am

First, date and time functions in SQL Server

1. GetDate () returns the current date and time of the system
Select GETDATE ()

2.DATEADD (interval,number,date) adds a period of time number to a specified date in the interval specified way, returning a new datetime value

Example: Add 2 days to the date
Select DATEADD (day,2, ' 2010-10-15 ')--return: 2010-10-17 00:00:00.000

And so on: it is also possible to reverse the date for a few days or months.

Select DATEADD (day,-2, ' 2010-10-15 ')--return: 2010-10-13 00:00:00.000

Select DATEADD (month,-2, ' 2010-10-15 ')--return: 2010-08-1 00:00:00.000

3. DateDiff (INTERVAL,DATE1,DATE2) returns the difference between Date2 and date1 two dates in interval specified way

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

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

4. DatePart (interval,date) returns the integer value corresponding to the specified portion of the date, interval

Select DATEPART (Month, ' 2004-10-15 ')--return 10

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

5. Datename (interval,date) returns the name of the string that corresponds to the specified part of the date, interval

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

Abbreviation
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

Second, date format conversion

Select CONVERT (varchar (11), time,) as time,* from TableName

Select Replace (replace (CONVERT (varchar, GETDATE (), 120), '-', '), ', '), ': ', ')--20040912110608

Select Test.date from Test where CONVERT (VARCHAR (), test.date,120) = ' 2015-08-09 '

Use of the time format conversion function convert () in SQL Server

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.