Top 30 SQL Date Time Function | All SQL Date-Time Function | Learn very important SQL Functions

1. ADD_DAYS Function  -

 

 SELECT ADD_DAYS (TO_DATE ('2009-12-05', 'YYYY-MM-DD'), 30) "add days" FROM DUMMY;

 

2. ADD_MONTHS Function -

 

SELECT ADD_MONTHS (TO_DATE ('2009-12-05', 'YYYY-MM-DD'), 1) "add months" FROM DUMMY;

 

3. ADD_MONTHS_LAST Function -

 

 SELECT ADD_MONTHS_LAST (TO_DATE ('2009-02-28', 'YYYY-MM-DD'), 1) "add months last" FROM DUMMY;

 

4. ADD_NANO100 Function  -

 

SELECT ADD_NANO100(TO_TIMESTAMP('1000-01-01 10:00:00.0000000'), 1) FROM DUMMY;

 

5. ADD_SECONDS Function  -

 

 SELECT ADD_SECONDS (TO_TIMESTAMP ('2012-01-01 23:30:45'), 60*30) "add seconds" FROM DUMMY;

 

6. ADD_YEARS Function -

 

SELECT ADD_YEARS (TO_DATE ('2009-12-05', 'YYYY-MM-DD'), 1) "add years" FROM DUMMY;

 

7. CURRENT_DATE Function

 

SELECT CURRENT_DATE "current date" FROM DUMMY;

 

8. CURRENT_TIME Function

 

SELECT CURRENT_TIME "current time" FROM DUMMY;

 

09. CURRENT_TIMESTAMP Function

 

- SELECT CURRENT_TIMESTAMP "current timestamp" FROM DUMMY;

 

10. CURRENT_UTCDATE Function -

 

 SELECT CURRENT_UTCDATE "UTCDate" FROM DUMMY;

 

11. DAYNAME Function -

 

SELECT DAYNAME ('2011-05-30') "dayname" FROM DUMMY;

 

12.DAYOFMONTH Function -

 

SELECT DAYOFMONTH ('2011-05-30') "dayofmonth" FROM DUMMY;

 

13.DAYOFYEAR Function -

 

SELECT DAYOFYEAR ('2011-05-30') "dayofyear" FROM DUMMY;

 

14.DAYS_BETWEEN Function -

 

SELECT DAYS_BETWEEN('2018-02-07 23:00:00',  '2018-02-08 23:00:00') AS sinceDays FROM dummy;

 

15.EXTRACT Function -

 

SELECT EXTRACT (YEAR FROM TO_DATE ('2010-01-04', 'YYYY-MM-DD')) "extract" FROM DUMMY; --{YEAR | MONTH | DAY | HOUR | MINUTE | SECOND}

 

16.HOUR Function-

 

 SELECT HOUR ('12:34:56') "hour" FROM DUMMY;

 

17.ISOWEEK Function -

 

SELECT ISOWEEK (TO_DATE('2011-05-30', 'YYYY-MM-DD')) "isoweek" FROM DUMMY;

 

18.LAST_DAY Function-

 

SELECT LAST_DAY (TO_DATE('2010-01-04', 'YYYY-MM-DD')) "last day" FROM DUMMY;

 

19.MINUTE Function -

 

SELECT MINUTE ('12:34:56') "minute" FROM DUMMY;

 

20.MONTH Function -

 

SELECT MONTH ('2011-05-30') "month" FROM DUMMY;

 

21.MONTHNAME Function -

 

 SELECT MONTHNAME ('2011-05-30') "monthname" FROM DUMMY;

 

22.MONTHS_BETWEEN Function -

 

SELECT MONTHS_BETWEEN(TO_DATE ('2003-01-01'), TO_DATE('2003-03-14')) "months_between" FROM DUMMY;

 

23.NANO100_BETWEEN Function

 

SELECT NANO100_BETWEEN ('2013-01-30', '2013-01-31') "nano100 between" FROM DUMMY;

 

24.NEXT_DAY Function

 

SELECT NEXT_DAY (TO_DATE ('2009-12-31', 'YYYY-MM-DD')) "next day" FROM DUMMY;

 

25.NOW Function

 

SELECT NOW () "now" FROM DUMMY;

 

26.SECOND Function

 

SELECT SECOND ('12:34:56') "second" FROM DUMMY;

 

27.SECONDS_BETWEEN Function -

 

SELECT SECONDS_BETWEEN ('2009-12-05', '2010-01-05') "seconds between" FROM DUMMY;

 

28.WEEK Function -

 

SELECT  WEEK ('2016-12-31') FROM DUMMY;

 

29.YEAR Function -

 

SELECT YEAR (TO_DATE ('2011-05-30', 'YYYY-MM-DD')) "year" FROM DUMMY;

 

30.YEARS_BETWEEN Function -

 

SELECT YEARS_BETWEEN(TO_DATE('2001-10-13'), TO_DATE('2003-01-14')) "years_between" FROM DUMMY;

Watch the below video for the explanation -