SQL Date and Time Processing set

SQL Server Date and Time Processing set

When the convert date conversion format is 20 or 120, the resulting string does not contain milliseconds.
For example, select convert (varchar (23), getdate (), 120 or 20)
21:46:43

When the date conversion format is 21 or 121, the resulting string is in milliseconds.
Select convert (varchar (23), getdate (), 121 or 21)
21:47:34. 360

Japanese Standard
When the date conversion format is 111, the resulting string is yyyy/mm/DD.
Select convert (varchar (23), getdate (), 111)
2003/01/07

For more information, see convert help.

Without Century digital (yy) and Century digital (yyyy) standard input/output **
-0 or 100 (*) Default Value: Mon dd yyyy hh: miam (or pm)
1 101 us mm/DD/YYYY
2 102 ANSI yy. Mm. dd
3 103 UK/French dd/mm/yy
4 104 German dd. mm. yy
5 105 Italian DD-mm-yy
6 106-dd mon YY
7 107-mon DD, YY
8 108-hh: mm: SS
-9 or 109 (*) Default Value + millisecond mon dd yyyy hh: MI: SS: mmmam (or pm)
10 110 US mm-dd-yy
11 111 YY/MM/DD in Japan
12 112 ISO yymmdd
-13 or 113 (*) European default value + millisecond dd mon yyyy hh: mm: SS: Mmm (24 h)
14 114-hh: MI: SS: Mmm (24 h)
-20 or 120 (*) ODBC specification yyyy-mm-dd hh: mm: ss [. fff]
-21 or 121 (*) ODBC specifications (in milliseconds) yyyy-mm-dd hh: mm: ss [. fff]
-126 (***) iso8601 yyyy-mm-dd thh: mm: SS: Mmm (without spaces)
-130 * Kuwait dd mon yyyy hh: MI: SS: mmmam
-131 * Kuwait dd/mm/yy hh: MI: SS: mmmam

SQL Server date calculation method 2 last day of last month

This is an example of calculating the last day of the last month. It is obtained by subtracting 3 ms from the last day of the month. Remember that the time in SQL Server is accurate to 3 ms. That's why I need 3 milliseconds to get the date and time I want.

Select dateadd (MS,-3, dateadd (mm, datediff (mm, 0, getdate (), 0 ))

The time part of the calculated date contains the time of the last day ("23: 59: 59: 997") that an SQL server can record.

Last day of last year

In the above example, to get the last day of last year, you need to subtract 3 ms from the first day of this year.

Select dateadd (MS,-3, dateadd (YY, datediff (YY, 0, getdate (), 0 ))

Last day of this month

Now, in order to get the last day of this month, I need to slightly modify the statements to get the last day of the previous month. For modification, you need to use datediff to compare the time interval returned by the current date and "" with 1. By adding a month, I calculate the first day of the next month, and then subtract 3 milliseconds, so that the last day of the month is calculated. This is the SQL script used to calculate the last day of the month.

Select dateadd (MS,-3, dateadd (mm, datediff (M, 0, getdate () + 1, 0 ))

Last day of this year

You should master this practice now. This is the script for calculating the last day of this year.

Select dateadd (MS,-3, dateadd (YY, datediff (YY, 0, getdate () + 1, 0 )).

The first Monday of this month

Now is the last example. Here I want to calculate the first Monday of this month. This is the computing script.

Select dateadd (wk, datediff (wk, 0,
Dateadd (DD, 6-datepart (day, getdate (), getdate ())
), 0)

In this example, I used the "Monday of this week" script and made some modifications. The modified part is to replace the "getdate ()" part in the original script with the 6th days of this month, in the calculation, the current date is replaced with the 6th day of this month so that the calculation can get the first Monday of this month.

Summary

I hope these examples will give you some inspiration when you use the dateadd and datediff functions to calculate the date. By using this mathematical method for calculating the time interval of a date, I found that useful calendars for displaying the interval between two dates are valuable. Note that this is only one way to calculate these dates. Remember, there are still many ways to get the same computing results. If you have other methods, it is not wrong. If you do not have them, I hope these examples will give you some inspiration, when you use the dateadd and datediff functions to calculate the date that your program may use.

Appendix: other date Handling Methods

1) Remove the hour, minute, and second

Declare @ datetime
Set @ = getdate () -- '2017-7-1 10:00:00'
Select @, dateadd (day, datediff (day, 0, @), 0)

　
2) display the day of the week

Select datename (weekday, getdate ())
3) how to obtain the number of days in a month

Declare @ M int
Set @ M = 2 -- month
Select datediff (day, '2017-'+ Cast (@ M as varchar) +'-15', '2017-'+ Cast (@ m + 1 as varchar) + '-15 ')

In addition, get the number of days this month
Select datediff (day, cast (month (getdate () as varchar) + '-' + Cast (month (getdate () as varchar) + '-15 ', cast (month (getdate () as varchar) + '-' + Cast (month (getdate () + 1 as varchar) + '-15 ')

Or use the script to calculate the last day of the month, and then use the day function area for the last day.
Select Day (dateadd (MS,-3, dateadd (mm, datediff (M, 0, getdate () + 1, 0 )))
4) determine whether a leap year is used:

Select case day (dateadd (mm, 2, dateadd (MS,-3, dateadd (YY, datediff (YY, 0, getdate (), 0 )))) when 28 then 'Year' else 'Year' end

Or
Select case datediff (day, datename (year, getdate () + '-02-01', dateadd (mm, 1, datename (year, getdate ()) + '-02-01 '))

When 28 then 'Year' else 'Year' end

　　
5) How many days of a quarter

Declare @ M tinyint, @ time smalldatetime
Select @ 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 10 end
Select @ time = datename (year, getdate () + '-' + convert (varchar (10), @ M) + '-01'
Select datediff (day, @ time, dateadd (mm, 3, @ time ))

SQL time/Date Processing
 
SQL time/Date Processing
In SQL statements, time (or date) is often processed. Below are some common statements: latency:
Sysdate + (5/24/60/60) latency of 5 seconds based on system time
Sysdate + 5/24/60 delay of 5 minutes based on system time
Sysdate + 5/24 latency of 5 hours based on system time
Sysdate + 5 delay of 5 days based on system time
Add_months (sysdate,-5) is delayed by May on the basis of system time
Add_months (sysdate,-5*12) delay of 5 years based on system time
Last month's date:
Select last_day (add_months (sysdate,-1) from dual;
Last second of this month:
Select trunc (add_months (sysdate, 1), 'mm')-1/24/60/60 from dual
Monday of this week:
Select trunc (sysdate, 'day') + 1 from dual
Number of days since the beginning of the year:
Select Ceil (sysdate-trunc (sysdate, 'Year') from dual;
Today is the week of this year:
Select to_char (sysdate, 'fmww') from dual
Today is the week of the month:
Select to_char (sysdate, 'ww ')-to_char (trunc (sysdate, 'mm'), 'ww') + 1 as "weekofmon" from dual
Number of days this month
Select to_char (last_day (sysdate), 'dd') days from dual
Number of days this year
Select add_months (trunc (sysdate, 'Year'), 12)-trunc (sysdate, 'Year') from dual
The date of next Monday
Select next_day (trunc (sysdate), 'monday') from dual
====================================
Workday Calculation Method
Create Table T (s date, e date );
Alter session set nls_date_format = 'yyyy-mm-dd ';
Insert into T values ('2017-03-01 ', '2017-03-03 ');
Insert into T values ('2017-03-02 ', '2017-03-03 ');
Insert into T values ('2017-03-07 ', '2017-03-08 ');
Insert into T values ('2017-03-07 ', '2017-03-09 ');
Insert into T values ('2017-03-05 ', '2017-03-07 ');
Insert into T values ('2017-02-01 ', '2017-03-31 ');
-- Assume that all dates do not contain time; otherwise, add trunc before all dates.
Select S, E, E-S + 1 total_days, trunc (e-S + 1)/7) * 5 + Length (replace (substr ('123 ', to_char (S, 'D'), MOD (E-S +), '0', '') work_days from T;
-- Drop table t;
========================================================== ======================
Determine whether the current time is morning afternoon or evening
Select case
When to_number (to_char (sysdate, 'hh24') between 6 and 11 then 'am'
When to_number (to_char (sysdate, 'hh24') between 11 and 17 then 'more'
When to_number (to_char (sysdate, 'hh24') between 17 and 21 then''
End
From dual;
========================================================== ============================

Processing Dates in Oracle
Converts a number to any time format. For example, seconds: You need to convert it to days/hours.
Select to_char (floor (trunc (936000/(60*60)/24 )) | 'day' | to_char (mod (trunc (936000/(60*60), 24) | 'hour' from dual
To_date format
Day:
Dd number 12
Dy abbreviated Fri
Day spelled out Friday
Ddspth spelled out, ordinal twelfth
Month:
MM number 03
Mon abbreviated MAR
Month spelled out March
Year:
YY two digits 98
Yyyy four digits 1998
The time range in the 24-hour format is: 0:00:00-23:59:59 ....
The time range in the 12-hour format is: 1:00:00-12:59:59 ....
1. Date and character conversion functions
Usage (to_date, to_char)
2.
Select to_char (to_date (222, 'J'), 'jsp ') from dual
Display two hundred twenty-two
3. Check the day of the week.
Select to_char (to_date ('1970-08-26 ', 'yyyy-mm-dd'), 'day') from dual;
Select to_char (to_date ('1970-08-26 ', 'yyyy-mm-dd'), 'day', 'nls _ date_language = American') from dual;

Set the date language
Alter session set nls_date_language = 'American ';
You can also
To_date ('1970-08-26 ', 'yyyy-mm-dd', 'nls _ date_language = American ')
4. Days of the two-day period
Select floor (sysdate-to_date ('20140901', 'yyyymmdd') from dual;
5. Use a time of null
Select ID, active_date from Table1
Union
Select 1, to_date (null) from dual;
Note that to_date (null) is used)
6.
A_date between to_date ('20140901', 'yyyymmdd') and to_date ('20140901', 'yyyymmdd ')
Therefore, it is not included in this range after on January 1, December 31 and before on January 1, December 1.
Therefore, when the time needs to be accurate, to_char is still necessary.
7. Date Format conflict
The input format depends on the type of the Oracle character set you have installed. For example:
Us7ascii. The date format is '01-Jan-01'
Alter system set nls_date_language = American
Alter session set nls_date_language = American
Or write it in to_date.
Select to_char (to_date ('1970-08-26 ', 'yyyy-mm-dd'), 'day', 'nls _ date_language = American') from dual;

Note that I just mentioned the nls_date_language. Of course there are many more, you can view
Select * From nls_session_parameters
Select * from V $ nls_parameters
8.
Select count (*)
From (select rownum-1 (rnum)
From all_objects
Where rownum <= to_date ('2017-02-28 ', 'yyyy-mm-dd')-to_date ('2017-
02-01 ', 'yyyy-mm-dd') + 1
)
Where to_char (to_date ('1970-02-01 ', 'yyyy-mm-dd') + rnum-1, 'd ')
Not
In ('1', '7 ')
Find the number of days between and except Monday and seven
Call dbms_utility.get_time before and after respectively, and then subtract the result (1/100 seconds instead of milliseconds ).
9.
Select months_between (to_date ('01-31-1999 ', 'Mm-DD-YYYY '),
To_date ('12-31-1998 ', 'Mm-DD-YYYY') "months" from dual;
1
Select months_between (to_date ('02-01-1999 ', 'Mm-DD-YYYY '),
To_date ('12-31-1998 ', 'Mm-DD-YYYY') "months" from dual;
1.03225806451613
10. next_day usage
Next_day (date, Day)
Monday-Sunday, for format code day
Mon-sun, for format code DY
1-7, for format code D
Select to_char (sysdate, 'hh: MI: ss') time from all_objects
Note: the time of the first record is the same as that of the last record.
11
You can create a function to solve this problem.
Create or replace function sys_date return date is
Begin
Return sysdate;
End;
Select to_char (sys_date, 'hh: MI: ss') from all_objects;
12. Hours
SQL> select sysdate, to_char (sysdate, 'hh') from dual;
Sysdate to_char (sysdate, 'hh ')
-----------------------------------------
2003-10-13 19:35:21 07
SQL> select sysdate, to_char (sysdate, 'hh24') from dual;
Sysdate to_char (sysdate, 'hh24 ')
-------------------------------------------
19:35:21 19
Obtain the year, month, and day.
13. Processing of the year, month, and day
Select older_date,
Newer_date,
Years,
Months,
ABS (
Trunc (
Newer_date-
Add_months (older_date, years * 12 + months)
)
) Days
From (select
Trunc (months_between (newer_date, older_date)/12) years,
MoD (trunc (months_between (newer_date, older_date )),
12) months,
Newer_date,
Older_date
From (select hiredate older_date,
Add_months (hiredate, rownum) + rownum newer_date
From EMP)
)
14. How to handle the uncertainty of the number of days in a month
Select to_char (add_months (last_day (sysdate) + 1,-2), 'yyyymmdd'), last_day (sysdate) from dual

16. Find the number of days this year
Select add_months (trunc (sysdate, 'Year'), 12)-trunc (sysdate, 'Year') from dual
How to deal with a leap year
To_char (last_day (to_date ('02 '|: Year, 'mmyyyy'), 'dd ')
If it is 28, it is not a leap year.
17. Differences between yyyy and RRRR
'Yyyy99 to_c
-----------
Yyyy 99 0099
Rrrr 99 1999
Yyyy 01 0001
Rrrr 01 2001
18. Processing in different time zones
Select to_char (new_time (sysdate, 'gmt', 'est '), 'dd/mm/yyyy hh: MI: ss'), sysdate
From dual;
19. One interval in five seconds
Select to_date (floor (to_char (sysdate, 'ssss')/300) * 300, 'ssss'), to_char (sysdate, 'ssss ')

From dual
To_date (FL to_ch
---------------
2007-01-01 60368
Sssss indicates five seconds
20. The day of the year
Select to_char (sysdate, 'ddd '), sysdate from dual
To _ sysdate
017 2007-01-17
21. Computing hour, minute, second, millisecond
Select
Days,
A,
Trunc (A * 24) hours,
Trunc (A * 24*60-60 * trunc (A * 24) minutes,
Trunc (A * 24*60*60-60 * trunc (A * 24*60) seconds,
Trunc (A * 24*60*60*100-100 * trunc (A * 24*60*60) mseconds
From
(
Select
Trunc (sysdate) days,
Sysdate-trunc (sysdate)
From dual
)
Select * From tabname
Order by deCODE (mode, 'fifo ', 1,-1) * to_char (RQ, 'yyyymmddhh24miss ');
Floor (date2-date1)/365) as year
Floor (date2-date1, 365)/30) as month
MoD (mod (date2-date1, 365), 30) as daily.
22. next_day Function
Next_day (sysdate, 6) is the next Friday from the current start. The following number is counted from Sunday.
1 2 3 4 5 6 7
October 6, 1234
Select (sysdate-to_date ('2017-12-03 12:55:45 ', 'yyyy-mm-dd hh24: MI: ss') * 24*60*60 from dual

The date returns the day and converts it to SS.

1. Current System Date and Time
Select getdate ()
　　
2. dateadd returns a new datetime value based on a period of time added to the specified date.
For example, add 2 days to the date
Select dateadd (day, 2, '2017-10-15 ') -- Return: 2004 00:00:00. 000
　　
3. datediff returns the number of date and time boundaries across two specified dates.
Select datediff (day, '2017-09-01 ', '2017-09-18') -- Return: 17
　　
4. datepart returns an integer representing the specified date of the specified date.
Select datepart (month, '2014-10-15 ') -- returns 10
　　
5. Return the string representing the specified date part of the specified date by datename
Select datename (weekday, '2017-10-15 ') -- Return: Friday
　　
6. Day (), month (), Year () -- compare it with datepart.
　　
Select current date = convert (varchar (10), getdate (), 120)
, Current time = convert (varchar (8), getdate (), 114)
　　
Select datename (DW, '2017-10-15 ')
　　
Select the week of the current year = datename (Week, '2017-10-15 ')
, Today is the day of the week = datename (weekday, '2017-10-15 ')
　　
　　
Function parameters/functions
Getdate () returns the current date and time of the system.
Datediff (interval, date1, date2) returns the difference date2-date1 between date2 and date1 in the way specified by interval

Dateadd (interval, number, date) in the format specified by interval, plus the date after the number
Datepart (interval, date) returns the integer corresponding to the specified part of the date.
Datename (interval, date) returns the string name corresponding to the specified part of interval in date.
　　
The value of interval is as follows:
　　
SQL Server (access and Asp) Description
Year YY yyyy-1753 ~ 9999
Quarter qq q quarter 1 ~ 4
Month mm m Month 1 ~ 12
Day of year dy y the number of days of the year, the day of the year 1-366
Day dd d, 1-31
Weekday DW w the number of days in a week, the day of the week 1-7
Week wk WW week, the week of the year 0 ~ 51
Hour hh h 0 ~ 23
Minute MI n minutes 0 ~ 59
Second ss s 0 ~ 59
Millisecond MS-millisecond 0 ~ 999
　　
　　
Access and ASP use date () And now () to obtain the system Date and Time. datediff, dateadd, and datepart can also be used in access and ASP. The usage of these functions is similar.