SQL Server Date and time functions

Source: Internet
Author: User
Tags dateformat

1, the common date method (below getdate () = ' 2006-11-08 13:37:56.233 ')

(1) Datename (DatePart, date)

Returns a String that represents the specified date portion of the specified date. DatePart See the list below.

Select Datename (DayGetdate-- return 8

(2) DATEPART (DatePart, date)

Returns an integer that represents the specified date portion of the specified date.

Select Datepart (yearGetdate-- return to 2006

(3) DATEADD (DatePart, number, date)

Returns a new datetime value that is added to the specified date after a time interval.

Select Dateadd 1 Getdate -- date plus one week after the current date

(4) DATEDIFF (DatePart, StartDate, EndDate)

Returns the number of date boundaries and time boundaries across two specified dates.

Select Datediff (Month'2006-10-11'2006-11-01')  -- returns 1

(5) Day (date)

Returns an integer that represents the day datepart portion of the specified date.

Select  Day (Getdate-- Date: 2014-11-08 08:56:45.903, return 8

(6) GETDATE ()

Returns the current system date and time as a datetime value in the SQL Server 2005 standard internal format.

Select Getdate -- back to 2006-11-08 13:37:56.2

(7) MONTH (date)

Returns an integer that represents the month portion of a specified date.

Select Month (Getdate-- return

(8) Year (date)

Returns an integer that represents the year part of the specified date.

Select  Year (Getdate-- return to 2006


2. Take a specific date

(1) The day of the week to get the current date

Select Datename Getdate -- Wednesday

(2) Calculate which day is the Monday of this week

Select Dateadd(Week,Datediff(Week,'1900-01-01',Getdate()),'1900-01-01' )  --back to 2006-11-06 00:00:00.000--orSelect Dateadd(Week,Datediff(Week,0,Getdate()),0)

(3) The first day of the current quarter

Select Dateadd Datediff 0 Getdate 0)  -- back to 2006-10-01 00:00:00.0

(4) How to get the number of days in a month

Select  Day Dateadd - 3 Dateadd Datediff 0 ' 2006-02-03 ' + 1 0 -- back to

(5) How many days of a quarter

Declare @m Tinyint,@time smalldatetimeSelect @m = Month(Getdate()) Select @m =  Case                   when @m between 1  and 3  Then 1                  when @m between 4  and 6  Then 4                  when @m between 7  and 9  Then 7                 Else Ten            EndSelect @time = Datename( Year,Getdate())+ '-' + Convert(Varchar(Ten),@m)+ '-01' Select Datediff( Day,@time,Dateadd(MM,3,@time))--back to


(6) Date of acquisition (YYYY-MM-DD)

Select Convert (Varchar(Getdate )- return to 2006-11-

3. Other

(1)--The following example specifies a date as a number. The database engine interprets 0 as January 1, 1900.

Select Month (0Day (0year (0)
-- The following two sentences are equivalent Select Datename 0 )Selectdatename'1900-01-01')


(2) SET Datefirst {number | @number_var}

Set the first day of the week to a number from 1 to 7.

Set 1 -- indicates that the first day of the week is "Monday" Select Datename Getdate ())  --WednesdaySelectDatepartGetdate--  Returns 3Select@ @DATEFIRST  -- view current settings


(3) SET DateFormat {format | @format_var}

Sets the order of the date parts (month/day/year) that are used to enter datetime or smalldatetime data.
... Valid parameters include MDY, dmy, Ymd, YDM, MyD, and Dym.
... This setting is used only in the interpretation when converting a string to a date value. It does not affect the display of date values.
... Set DateFormat settings are set at execution or runtime, not at parse time.
... Set DateFormat overrides the implicit date format setting for set LANGUAGE.
Here is an example:

--Set Date format to year, day, month.SetDateFormat YDM;GoDeclare @datevar Datetime;Set @datevar = '1998/31/12';Select @datevar  asDatevar;Go--Set Date format to year, month, day.SetDateFormat Ymd;GoDeclare @datevar Datetime;Set @datevar = '1998/12/31';Select @datevar  asDatevar;Go


(4) List of date parts

Date part

Abbreviation

Year

YY, yyyy

Quarter

QQ, Q

Month

MM, M

DayOfYear

Dy, y

Day

DD, D

Week

WK, WW

Weekday

Dw

Hour

hh

Minute

MI, n

Second

SS, S

Millisecond

Ms

Data type

Range

Accuracy

Datetime

January 1, 1753 to December 31, 9999

3.33 ms

smalldatetime

January 1, 1900 to June 6, 2079

SQL Server Date and time functions

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.