Oracle SQL Date Comparison

Oracle SQL Date Comparison:
Before today:

SELECT * from up_date where update < To_date (' 2007-09-07 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')
SELECT * from up_date where update <= to_date (' 2007-09-07 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')

After today only:

SELECT * from up_date where update > To_date (' 2007-09-07 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')
SELECT * from up_date where update >= to_date (' 2007-09-07 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')

Exact time:

SELECT * from up_date where update = To_date (' 2007-09-07 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')

Within a certain period of time:

SELECT * from up_date where update between to_date (' 2007-07-07 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ') and To_date (' 2007-09-07 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')
SELECT * from up_date where update < To_date (' 2007-09-07 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ') and update > to_date (' 20 07-07-07 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')
SELECT * from up_date where update <= to_date (' 2007-09-07 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ') and ' Update >= to_date ' (' 2007-07-07 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')

1. Current system date, time Select GETDATE () 2. DateAdd returns a new datetime value based on adding a period of time to the specified date Example: Add 2 days to the date Select DATEADD (day,2, ' 2004-10-15 ')--return: 2004-10-17 00:00:00.000 3. DateDiff returns the number of date and time boundaries across two specified dates. Select DateDiff (Day, ' 2004-09-01 ', ' 2004-09-18 ')--return: 17 4. DatePart returns an integer representing the specified date portion of the specified date. SELECT DATEPART (month, ' 2004-10-15 ')--return 10 5. Datename returns a string representing the specified date part of the specified date SELECT Datename (Weekday, ' 2004-10-15 ')--return: Friday 6. Day (), month (), year ()--can be compared with datepart Select Current date =convert (varchar), GETDATE (), 120) , Current time =convert (varchar (8), GETDATE (), 114) 7. Select Datename (DW, ' 2004-10-15 ') Select how many weeks of the year =datename (Week,getdate ()) , today is the week of =datename (Weekday,getdate ()) function parameters/Functions GetDate ()--Returns the current date and time of the system DateDiff (INTERVAL,DATE1,DATE2)--Returns the difference between Date2 and date1 two dates in interval specified mode date2-date1 DATEADD (interval,number,date)--The date after number is added in the way specified by interval DatePart (interval,date)---Returns the integer value corresponding to the specified portion of the date, interval Datename (interval,date)--Returns the string name corresponding to the specified part of the date, interval The setting values for the parameter interval are as follows: Value abbreviations (SQL Server) Access and ASP descriptions Year Yy yyyy 1753 ~ 9999 Quarter Qq Q Season 1 ~ 4 Month Mm M 1 ~ 12 Day of the year Dy y the number of days of the year, the day of the first 1-366 Day Dd D, 1-31 Weekday Dw W Day of the week, day of the week 1-7 Week Wk WW Week, week of the year 0 ~ 51 Hour Hh H 0 ~ 23 Minute Mi N min 0 ~ 59 Second Ss s seconds 0 ~ 59 Millisecond MS-MS 0 ~ 999 In Access and ASP, date () and now () are used to obtain system datetime, where Datediff,dateadd,datepart is also used in Access and ASP, and the usage of these functions is similar Example: 1.GetDate () for SQL Server:select GetDate () 2.DateDiff (' s ', ' 2005-07-20 ', ' 2005-7-25 22:56:32 ') returns a value of 514,592 seconds DateDiff (' d ', ' 2005-07-20 ', ' 2005-7-25 22:56:32 ') returns a value of 5 days 3.DatePart (' W ', ' 2005-7-25 22:56:32 ') returns a value of 2 that is Monday (Sunday is 1, Saturday is 7) DatePart (' d ', ' 2005-7-25 22:56:32 ') returns a value of 25, or 25th DatePart (' y ', ' 2005-7-25 22:56:32 ') returns a value of 206 that is the No. 206 Day of the Year DatePart (' yyyy ', ' 2005-7-25 22:56:32 ') returns a value of 2005 that is 2005 The SQL Server DATEPART () function returns a portion of the SQL Server datetime field. The syntax for the SQL Server DATEPART () function is: DATEPART (Portion, datetime) where datetime is the SQL Server datetime field and the name of the section is one of the following: Ms for Milliseconds Yy for year Qq for Quarter of the year Mm for Month Dy for the day of the year Dd for day of the Month Wk for Week Dw for the day of the Week Hh for Hour Mi for Minute Ss for Second --1. Write a function that implements the output time information (March 16, 2009 Monday pm) According to ' date of month, day of the week, morning afternoon ' Select Datename (Yy,getdate ()) + ' year ' + Datename (Mm,getdate ()) + ' month ' + Datename (Dd,getdate ()) + ' Day ' + Datename (Weekday,getdate ()) + Case when Datename (Hh,getdate ()) < "I ' am ' Else ' PM ' End --2. Write the function, depending on the input time. The day of the year is output Select DATEPART (dy,getdate ()) --3. Finding random output characters ' A-Z Select char (97+abs (checksum (NEWID))%26) Select char (97+rand () *26)

Oracle SQL Date Comparison