SQL: TIMESTAMP and DATE usage and conversion in SQL statement

A. TIMESTAMP

1. Use CURRENT_TIMESTAMP to insert the current timestamp into your SQL statement. For example,

      Insert into STAR_MOVE values ('MARS', 'HIGH', 'MIDSKY', CURRENT_TIMESTAMP);

      SELECT CURRENT_TIMESTAMP, NAME, STATUS FROM STAR_MOVE;

2. Use the TO_TIMESTAMP to convert a string to timestamp.

      SELECT STAR_NAME FROM STAR_MOVE
      WHERE InsertedTime > TO_TIMESTAMP('23/09/2015', 'dd/mm/yyyy');

3. Uset TO_CHAR to make a timestamp a string.

      SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') from dual;
 
      SELECT STAR_NAME, TO_CHAR(InsertedAt, 'DD/MM/YYYY HH24:MI')
      FROM STAR_MOVE

B. DATE

1. Use SYSDATE to put the current date into your SQL satement. For example,

      Insert into StartOfSeasons values ('Spring', SYSDATE);

2. Use TO_DATE to convert a string to date.

      Insert into StartOfSeason('Spring', TO_DATE('2016/03/15:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));

      SELECT SEASON FROM StartOfSeason
      WHERE SeasonStartDate > TO_DATE('01-03-2015', 'DD-MM-YYYY');

3. Use TO_CHAR to display a date in a particular format.

      SELECT NAME, TO_CHAR(SeasonStartDate, 'YYY/MM/DD') FROM StartOfSeasons;
      SELECT TO_CHAR(SYSDATE, 'DD-Mon-YYYY HH24:MI:SS') FROM dual;

For both timestamp and date

Use TRUNC to convert a long date or timestamp to a short date.

      select trunc(to_date('2016-05-31 23:12:05', 'YYYY-MM-DD HH24:MI:SS')) from dual;
      The output is 31-MAY-16.

      select trunc(to_date('2015-05-15', 'YYYY-MM-DD')) from dual;
      The output is 15-MAY-15.

      select trunc(current_timestamp) from dual;
      The output is 11-MAR-16

       select 'test' from dual where trunc(to_date('2016-05-31 23:12:05', 'YYYY-MM-DD HH24:MI:SS')) > to_date('2015-05-15', 'YYYY-MM-DD');    



                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.