Shulou(Shulou.com)06/03 Report--

This article mainly introduces the SQLServer date function example analysis, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

With the development of SQL Server up to now, there are traditional methods to control the format of dates, such as CONVERT (), and there are also more convenient new methods, such as FORMAT (); similarly, the operation functions on dates are also divided into traditional methods: DATEADD (), etc., and there are also convenient new methods: EOMonth (), etc.

First, the format of the date

Formatting refers to converting date types (Date), date, and time types to character types, usually using the CONVERT () and FORMAT () functions.

1, traditional CONVERT ()

SQL Server controls the display format of the date, usually using the CONVERT () function, by controlling the style parameter to control the format of the date display, but there are a lot of style, which is not good for memory.

CONVERT (data_type [(length)], expression [, style])

Style in line with oriental reading habits and its display format are as follows:

101: mm/dd/yyyy

110: mm-dd-yyyy

111: yyyy/mm/dd

112: yyyymmdd

120: yyyy-mm-dd hh:mm:ss

121: yyyy-mm-dd hh:mm:sssssss

The style of the CONVERT () function is a number, which is difficult to remember and can only be displayed in a system-defined format, which is not flexible enough. SQL Server provides a more flexible conversion function, FORMAT ().

2, convenient FORMAT () function

FORMAT () function, can easily and flexibly control the display format of numeric, date and time types. In general, the FORMAT () function is mainly used to format and display date/time types and numeric types, the parameter format is used to specify the display format, giving users more freedom to control the format, the culture parameter is optional, and is used to specify the display language. The data type of the return value of this function is NVARCHAR, if the format conversion fails. This function returns NULL:

FORMAT (value, format [, culture])

The parameter format uses # to represent a numeric value, and the parameter format uses the following placeholder to represent the date / time format:

Yyyy, MM, dd: indicates year, month and day

Hh:mm:ss fffffff: indicates hours, minutes, seconds, milliseconds

Use "/", "-", etc., as split symbols to connect various parts (part)

(1) format date/time

Specify the format of the date / time display in the format parameter, in a specific format: "yyyy:MMdd hh:mm:ss fffffff" explicit date / time, for example:

Select format (SYSDATETIME (), 'yyyy-MM-dd hh:mm:ss fffffff')

(2) convert numerical types

Use # to represent a number in the parameter format, and use the corresponding connector to concatenate the format characters into numbers, for example:

FORMAT (123456789) AS 'Custom Number Result

Second, the structure of date and time

The commonly used date components (datepart) are: year, month, day, hour, minute, second, ns, TZoffset (abbreviated as tz)

DATEPART (datepart, date) YEAR (date) MONTH (date) DAY (date)

In the actual product environment, weeks, quarters, etc., are very useful:

Quarter: quarter, with a range of 1, 2, 3, 4

Week: the ordinal number of weeks in the middle of the year. The range is 1-53.

Dayofyear: the ordinal number of days in the middle of the year, with a value range of 1-366,

Weekday: the ordinal number of days in a week, with a range of 1-7

The datepart returned by DATEPART () is of type int. If you want to return a character type, you can use the DATENAME () function:

DATENAME (datepart, date)

To construct a date through datepart, the common functions are:

DATEFROMPARTS (year, month, day) DATETIME2FROMPARTS (year, month, day, hour, minute, seconds, fractions, precision) DATETIMEOFFSETFROMPARTS (year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision) TIMEFROMPARTS (hour, minute, seconds, fractions, precision)

The parameter precision refers to the precision of decimal seconds, and refers to the n value in DateTime2 (n), DateTimeOffset (n), and Time (n), which indicates how many decimal places are used to represent 1s.

Third, date operation

Date functions: EOMonth, Format, DateAdd, DateDiff, SwitchOffset

1, the last day of the month

The function EOMonth () returns the last day of the specified date

EOMONTH (start_date [, month_to_add])

Parameter comments:

Start_date: there are two input methods, which can be converted to Date string type and date data type

Month_to_add: is of type int and can be positive integers, negative integers and 0. The default value is 0. If omitted, the default value is 0.

For example, view the last day of the current month, the last day of the next month, and the last day of the previous month:

Declare @ date dateset @ date=getdate () select EOMONTH (@ date) as CurrentMonth_EndDay, EOMONTH (@ date,1) as NextMonth_EndDay, EOMONTH (@ date,-1) as LastMonth_EndDay

2, the first day of the month

Using the DateFromParts () function, you can get the date type from three positive integers (year,month,day). Just set the day parameter to 1 to get the date of the first day of the month.

Declare @ date dateset @ date=getdate () select DATEFROMPARTS (year (@ date), month (@ date), 1)

You can also use the Format () function to return the first day of the month as a string, for example, to get the first day of the current month:

FORMAT (GETDATE (), 'yyyyMM01')

3, switch the time zone

Switch the data of DateTimeOffset type to the specified time zone. During the conversion, the UTC time is fixed. According to the fixed UTC time, the local time of the specific time zone is switched to:

SWITCHOFFSET (DATETIMEOFFSET, time_zone)

Parameter comments:

Variables of type DATETIMEOFFSET:DateTimeOffset (n)

Time_zone: the specified target time zone data in the format of [+ | -] hh:mm

Use the SwitchOffset () function to switch the time zone offset (Offset) of DateTimeOffset to the specified time zone, for example, from the local time zone East eighth to East Seven:

DECLARE @ remote DATETIMEOFFSET DECLARE @ local DATETIMEOFFSETSET @ local = SYSDATETIMEOFFSET () SET @ remote = SWITCHOFFSET (@ local,'+ 07 local DATETIMEOFFSETSET 00') SELECT @ remote AS remote_time,@local AS local_time

As you can see, the time in East 7 is one hour later than that in East 8.

4, what day is the current date

In SQL Server, the first day of the week is set through the DataFirst option, with the ordinal from 1 to 7, indicating the seven days of the week.

SET DATEFIRST {number | @ number_var}

(1) you can get the set value through @ @ datefirst

Set DATEFIRST 1select @ @ datefirst

(2) use the function datepart function to get the day of the day.

Set DATEFIRST 1select datepart (WEEKDAY,getutcdate ()) set DATEFIRST 2--select @ @ datefirstselect datepart (WEEKDAY,getutcdate ())

Because setting different DateFirst will cause datepart to return different values, you must use @ @ DateFirst

Set DATEFIRST 2select Datepart (weekday, getdate () + @ @ datefirst-1) set DATEFIRST 1select Datepart (weekday, getdate () + @ @ datefirst-1)

4. Use DateName to get the name of WeekDay

The name of WeekDay is related to the language setting of the system, but not to the setting of DateFirst.

(1) View current language settings

Select @ @ language

(2) View the languages supported by the system

Select alias,name, * from sys.syslanguages

(3) set the language

Set LANGUAGE 'Simplified Chinese'set LANGUAGE' us_english'

(4) use DateName to obtain the name of WeekDay

Set LANGUAGE 'Simplified Chinese'select DATENAME (WEEKDAY,getutcdate ()) set LANGUAGE' us_english'select DATENAME (WEEKDAY,getutcdate ()) IV. Date function

Sqlserver_ time format _ year, month, day

Example description of function name parameter

Dateadd (date part, number, date)

Select dateadd (year,45,'1990-12-11') returns 2035-12-11 00 rime 00.000

Select dateadd (month,45,'1990-12-11') returns 1994-09-11 00 rime 00.000

Select dateadd (mm,45,'1990-12-11') returns 1994-09-11 00 rime 00.000

Select dateadd (qq,12,'1990-12-11') returns 1993-12-11 00 rime 00.000

Select dateadd (hh,12,'1990-12-11') returns 1990-12-11 12 purl 00.000

Select dateadd (yy,-12,'1990-12-11') returns 1978-12-11 00 rime 00.000

Returns the new date value after adding a time interval to the specified date.

Number: the value used to add to the specified date part. If a non-integer value is specified, the fractional portion of the value is discarded and is not rounded when it is discarded.

Date: the specified original date

In this function: dw,dy,dd effect is the same as days.

Datediff (date section, start date, end date)

Select datediff (yy,'1990-12-11) returns 18

Selectdatediff (mm,'2007-12-11) returns 9

Returns the integer value of the difference between the specified date portions of two specified dates.

In this function, the dw,dy,dd effect is the same as days.

Datename (date part, date)

Select datename (mm,'2007-12-11') returns 12select datename (dw,'2007-12-11') returns Tuesday select datename (dd, '2007-12-11') returns 11select datename (wk,' 2007-12-11')-50 the week ordinal of select datename (dy, '2007-12-11')-345 the day of the year select DATENAME (YYYY,GETDATE ()) + DATENAME (MM,GETDATE ())-201803

Returns a string that represents the specified date portion of the specified date.

Dw indicates what day of the week it is.

Wk means the week ordinal of a year.

Dy means the day ordinal of the year.

Datepart (date part, date)

Select datepart (mm,'2007-12-11') returns 12select datepart (dw,'2007-12-11') returns 3select datepart (dd, '2007-12-11') returns 11select DATEPART (YYYY,GETDATE ()) + DATENAME (MM,GETDATE ())-2021

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

Wk means the week ordinal of a year.

Dy means the day ordinal of the year.

Dw represents the day of the week, and returns an integer that defaults to Sunday.

Getdate () has no parameters

Select getdate () returns 2222-02-22 12-12-34-19. 070

Returns the current system date and time.

Getutcdate () has no parameters

Select getutcdate () returns 2222-02-22 04Fren 34 19.073

Returns the current UTC (Universal Standard time) time. Greenwich mean time (GMT)

1. The first day of last month

SELECT CONVERT (CHAR (10), DATEADD (month,-1,DATEADD (dd,-DAY (GETDATE ()) + 1MagneGETDATE (), 111)

2. The last day of last month

SELECT CONVERT (CHAR (10), DATEADD (ms,-3,DATEADD (mm, DATEDIFF (mm,0,getdate ()), 0)) +'23 DATEADD 59 ms,-3,DATEADD'

3. The first day of this month

SELECT CONVERT (CHAR (10), DATEADD (dd,-DAY (GETDATE ()) + 1) GETDATE (), 111)

4. The last day of the month

SELECT CONVERT (CHAR (10), DATEADD (ms,-3,DATEADD (mm,DATEDIFF (mmem0recentgetdate ()) + 1Magne0)), 111) + '23Rose 59GetDet 59'

5. The last first day of the month

SELECT CONVERT (CHAR (10), DATEADD (dd,-DAY (GETDATE ()) + 1 (GETDATE (), 111)

6. The last day of the coming month

SELECT CONVERT (CHAR (10), DATEADD (ms,-3,DATEADD (mm,DATEDIFF (mmem0recentgetdate ()) + 2mem0)), 111) + '23Rose 59GetDet 59'

The meaning of the date part:

Abbr. Yearyear yy,yyyy

Quarter season qq,q

Month month mm, m

Dayofyear days (see instructions in the function) dy, y

Day days (see instructions in the function) dd, d

Week week wk, ww

Weekday days (see instructions in the function) dw, w

Hour hour hh

Minute minutes mi, n

Second seconds ss, s

Millisecond millisecond ms

=

Sql Server queries data within the specified range (week, January, week, month, etc.)

1. Query all the data for today

Select * from table name where datediff (day, field name, getdate ()) = 0

two。 Query all the data of yesterday

Select * from table name where datediff (day, field name, getdate ()-1) = 0

3. Query data for which the date of the day is one week ago

Selcet * from table name where datediff (week, field name, getdate ()-1) = 0

4. Query the data of the first 30 days

Select * from table name where datediff (d, field name, getdate ())

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.