Date Functions in SQL

We are going to look at different DATE functions in SQL world. SQL developers always face few issues to deal with DATE functions in the initial phase of learning process. The reason is, in our daily lives, we deal very less manipulation with date as a variable (calculation of age may one most of us know)

Once we enter into data management, DATE carries very heavy weightage as all the event / transaction in the business carries DATE as the important component. So, business inquires more about events based on the DATE. When did this sales transaction happen? What is the next due date of this loan? What is the duration of the loan in months, quarters and years? How many months are already over in this loan? How much EMI we will receive in the month of APR 24? How many more days left for next EMI? After Due date, how many days he / she took to pay the EMI? What is the interest we charge if they paid late by one week? List is limit less.

What does DATE data type consist of? Think about it. If you think about a number data type, every one of us know. We can do any arithmetic operations on the number which makes it easy. Same thing cannot be applied to date as it has multiple components (Day, month, year, time up to seconds in oracle, each RDBMS has its own format). This makes it complex to deal with especially if you are doing any calculations based on this data type.

We will start with a date function which gives today's DATE

SELECT SYSDATE FROM DUAL;

SYSDATE is a date function which gives today's date in 'dd-mon-yy' format if its oracle. Default date format in oracle is DD-MON-YY format. If you are working in other RDBMS the default format may change. So, understand the format of the DATE which is very important to begin with.

We can add two numbers. Can we add two dates ('01-JAN-2024' and '05-MAR-2024')? What do you expect in this scenario. Is this make sense or no?

We can subtract two numbers. Can we subtract two dates ('01-JAN-2024' and '01-FEB-2024')? Think about it. what is that you are expecting as a result.

In the above pointers we cannot add two dates, but we can subtract two dates. So, not all the arithmetic operators wont work in DATE. That makes it little difficult to understand in the initial days. So, be aware of the same.

In the ecommerce data sets, the sales_transaction happens today. After 2 days delivery happens. So, we have to know what date we deliver the product. How do you derive delivery date based on the transaction date. For example transaction date is '01-FEB-24' what kind of operation you do to get the delivery date as '03-FEB-24'. What operation you do '01-FEB-24' to derive '03-FEB-24'.

So, if you recollect how to get todays date, we use SYSDATE.

SELECT SYSDATE FROM DUAL; -- it gives todays DATE.

to add two days to current date, we write

SELECT SYSDATE + 2 FROM DUAL; -- it gives the date after two days from today. So, we can add or subtract days (in number) to date data types.

Now we will try to get the age of the person whose DOB is '01-FEB-99'. How will you derive the age of a person? What's your logic? Think what other date we need to calculate age. DOB is one, TODAYS date is derived from SYSDATE function. We can subtract DOB from SYSDATE to know the number of days. We convert that number of days into Year by dividing that number by 365.

SELECT SYSDATE - TO_DATE('01-FEB-99') NO_DAYS FROM DUAL; -- this gives the difference between two dates in days.

SELECT (sysdate - to_date('01-FEB-99') ) / 365 Age FROM DUAL; -- this will give the age of the person. You will decimal value in the result (25.06993477295788939624556062912227295789). You can use round of function to let go the decimal and show only the whole number.

SELECT ROUND((SYSDATE - TO_DATE('01-FEB-99')) / 365,0) Age FROM DUAL;

There are multiple ways to get, but you have to follow your logic to deal. We will continue with few more examples tomorrow. Sleep on this if you are dealing with DATE for the first time.

We will explore months_between, trunc, to_char, to_date functions in near future. Happy SQL Learning,