SQL Server convert datetime format conversion

Source: Internet
Author: User

Convert (, 120) 100 or 0 mon dd yyyy hh: miam (or PM) 101mm/DD/yy 102 yy. mm. DD 103 dd/mm/yy 104 DD. mm. YY 105 DD-mm-yy 106 dd mon YY 107 mon DD, YY 108 hh: mm: SS 109 or 9 Mon dd yyyy hh: MI: SS: mmmam (or pm) 110mm-dd-yy 111 YY/MM/DD 112 yymmdd 113 or 13 dd mon yyyy hh: mm: SS: Mmm (24 h) 114 hh: MI: SS: mm (24 h) 120 or 20 yyyy-mm-dd hh: MI: SS (24 h) 121 or 21 yyyy-mm-dd hh: MI: Ss. mmm (24 h) 126 yyyy-mm-ddthh: mm: Ss. mmm (no space) 130 dd mon yyyy hh: MI: SS: mmmam 131

 

 

1st Page SQL Server convert datetime format conversion

Set the value of the [datetime] field of the table in SQL Server to '2017-11-07 16:41:35. change 033' to '2017-11-07 00:00:00 'to delete the time, minute, and second. [datetime] the field must be of the datetime type. update table set [datetime] = convert (char (11), [datetime], 120)

Obtain the current date and convert it to the required datetime format using convert.

Select convert (varchar (12), getdate (), 112)

20040912

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

Select convert (varchar (12), getdate (), 102)

2004.09.12

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

Select convert (varchar (12), getdate (), 101)

09/12/2004

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

Select convert (varchar (12), getdate (), 103)

12/09/2004

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

Select convert (varchar (12), getdate (), 104)

12.09.2004

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

Select convert (varchar (12), getdate (), 105)

12-09-2004

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

Select convert (varchar (12), getdate (), 106)

12 09 2004

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

Select convert (varchar (12), getdate (), 107)

09 12,200 4

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

Select convert (varchar (12), getdate (), 108)

11:06:08

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

Select convert (varchar (12), getdate (), 109)

09 12, 2004 1

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

Select convert (varchar (12), getdate (), 110)

09-12-2004

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

Select convert (varchar (12), getdate (), 113)

12 09 2004 1

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

Select convert (varchar (12), getdate (), 114)

11:06:08. 177

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

Declare @ datetime -- defines a datetime variable

Set @ datetime = getdate (); -- Obtain the current system time and assign it to the @ datetime Field

-- Short Date Format: yyyy-m-d

Select Replace (convert (varchar (10), @ datetime, 120), N'-0 ','-')

-- Long Date Format: yyyy-mm-dd

Select stuff (convert (char (8), @ datetime, 112), 5, 0, N 'Year'), 8, 0, N 'month') + N'

-- Long Date Format: yyyy-mm-dd

Select datename (year, @ datetime) + N 'Year' + Cast (datepart (month, @ datetime) as varchar) + N 'month' + datename (day, @ datetime) + n'day'

-- Complete date + time format: yyyy-mm-dd hh: MI: SS: Mmm

Select convert (char (11), @ datetime, 120) + convert (char (12), @ datetime, 114)

Bytes ------------------------------------------------------------------------------------------------

2. Date Calculation

Declare @ DT datetime

Set @ dt = getdate ()

Declare @ number int

Set @ number = 3

-- 1. Specify the first or last day of the year

-- A. The first day of the year

Select convert (char (5), @ DT, 120) + '1-1'

-- B. The last day of the year

Select convert (char (5), @ DT, 120) + '12-31'

-- 2. Specify the first or last day of the quarter where the date is located.

-- A. The first day of the quarter

Select convert (datetime, convert (char (8), dateadd (month, datepart (quarter, @ DT) * 3-month (@ DT)-2, @ DT), 120) + '1 ')

-- B. The last day of the quarter)

Select convert (datetime, convert (char (8), dateadd (month, datepart (quarter, @ DT) * 3-month (@ DT), @ DT), 120)

+ Case when datepart (quarter, @ DT) in (1, 4)

Then '31 'else' 30' end)

-- C. The last day of the quarter (Direct Algorithm)

Select dateadd (day,-1, convert (char (8), dateadd (month, 1 + datepart (quarter, @ DT) * 3-month (@ DT ), @ DT), 120) + '1 ')

-- 3. Specify the first or last day of the month in which the date is located.

-- A. The first day of the month

Select convert (datetime, convert (char (8), @ DT, 120) + '1 ')

-- B. The last day of the month

Select dateadd (day,-1, convert (char (8), dateadd (month, 1, @ DT), 120) + '1 ')

-- C. The last day of the month (the easy-to-use error method)

Select dateadd (month, 1, dateadd (day,-day (@ DT), @ DT ))

-- 4. Specify any day of the week of the date

Select dateadd (day, @ number-datepart (weekday, @ DT), @ DT)

-- 5. Specify any day of the week of the date

-- A. Sunday is the 1st day of a week.

Select dateadd (date, @ number-(datepart (weekday, @ DT) + DATEFIRST-1) % 7, @ DT)

-- B. Monday is the 1st day of a week.

Select dateadd (day, @ number-(datepart (weekday, @ DT) + DATEFIRST-2) % 7-1, @ DT) (Source: Skynet)

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.