Conversion Data of SQL Server-convert datetime format

Source: Internet
Author: User

In SQL Server, I often need to perform some time-type field conversions, but I don't quite remember it. So I collected the following sqlserverconvert datetime-related materials and posted them on my site, it is convenient for you to search for it later. I hope it will be helpful to you.

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 push 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 (day, @ 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)

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.