PostgreSQL

To_Date PostgreSQL

TO_DATE is one of the most important functions of PostgreSQL. In our previous articles, we have been exploring the usage of the different functions and commands of PostgreSQL. This article will also focus on the usage of the TO_DATE function of PostgreSQL in Windows 10. We will first briefly introduce you to this function, followed by a few relevant examples that will depict its usage.

To_Date Function in PostgreSQL in Windows 10:

The TO_DATE function of PostgreSQL is used to convert the provided string into a proper date in the YYYY-MM-DD output format. This function of PostgreSQL accepts two arguments, i.e., the date string that you want to be converted into a proper date and the format of the provided date string. The second argument of this function should work strictly with the first argument, i.e., you should use the same format in which you will write the date in the date string as the second argument of the TO_DATE function. For example, if the first argument is “31032021” i.e., date, month, and year, the second argument should be “DDMMYYYY”. We have formulated the following five examples for you through which you will be able to understand the usage of the TO_DATE function in PostgreSQL in Windows 10.

Note: Remember to log into your PostgreSQL server through your Windows 10 system’s terminal before executing these examples on your system.

Example # 1: When the Provided Date String is in the “DDMMYYYY” Format:

To illustrate this example, we will run the following query in our PostgreSQL console:

# SELECT TO_DATE(‘10092021’, ‘DDMMYYYY’);

In this query, we have passed a random date string in the “DDMMYYYY” format as the first argument to the TO_DATE function, followed by its format as the second argument of this function. Both of these arguments are enclosed in commas since they both are strings.

Once we run this query, the passed string will be instantly converted to date, as shown in the image below:

Example # 2: When the Provided Date String is in the “YYYYMMDD” Format:

Now, we want to slightly change the order of the date provided to the TO_DATE function in the previous example and see if it is still an acceptable argument or not. To illustrate this example, we will run the following query in our PostgreSQL console:

# SELECT TO_DATE(‘20210910’, ‘YYYYYMMDD’);

In this query, we have passed a random date string in the “YYYYMMDD” format as the first argument to the TO_DATE function, followed by its format as the second argument of this function. Both of these arguments are enclosed in commas since they both are strings.

Once we run this query, the passed string will be instantly converted to date, as shown in the image below:

Example # 3: When the Provided Date String is in the “DD Mon YYYY” Format:

We know that instead of passing the month in the form of a number, we can also pass it in string literals. However, the format for this type of date string will change a little bit. To illustrate this example, we will run the following query in our PostgreSQL console:

# SELECT TO_DATE(’10 Sep 2021’, ‘DD Mon YYYY’);

In this query, we have passed a random date string in the “DD Mon YYYY” format as the first argument to the TO_DATE function, followed by its format as the second argument of this function. Both of these arguments are enclosed in commas since they both are strings. Although this syntax of passing a date to the TO_DATE function also seems quite simple, you especially need to take care while passing the name of the month in this case since you are only supposed to write the first three initials of the name of the month. For example, if you are passing the date of January, then you will denote the month as Jan. Moreover, if you are passing the month in this format, then in the second argument of the TO_DATE function, you must write “Mon” instead of “MM”.

Once we run this query, the passed string will be instantly converted to date, as shown in the image below:

Example # 4: When the Provided Date String is in the “YYYY Mon DD” Format:

Now, we want to slightly change the order of the date provided to the TO_DATE function in the previous example and see if it is still an acceptable argument or not. To illustrate this example, we will run the following query in our PostgreSQL console:

# SELECT TO_DATE(’2021 Sep 10’, ‘YYYY Mon DD’);

In this query, we have passed a random date string in the “YYYY Mon DD” format as the first argument to the TO_DATE function, followed by its format as the second argument of this function. Both of these arguments are enclosed in commas since they both are strings.

Once we run this query, the passed string will be instantly converted to date, as shown in the image below:

Example # 5: When the Provided Date String is Irrelevant or Erroneous:

The TO_DATE function of PostgreSQL is so efficient that, along with simply converting the provided string to the date, it is also capable of checking whether the provided date string is valid or not. For example, if we talk about months, we know that they cannot exceed the number 12. Similarly, every month of the year has a different number of days. Therefore, the TO_DATE function can also check whether the provided day is valid for that particular month or not. To illustrate this example, we will execute the following query in our PostgreSQL console:

# SELECT TO_DATE(‘2021 Sep 31’, ‘YYYY Mon DD’);

In this query, we have passed a random date string in the “YYYY Mon DD” format as the first argument to the TO_DATE function, followed by its format as the second argument of this function. Both of these arguments are enclosed in commas since they both are strings. However, we all know that the month of September has 30 days, not 31. Let us find out if an error message is generated after running this query or not.

In the image shown below, you can see that an error message was generated once we executed the above-mentioned query because the date that we provided was invalid.

Conclusion:

We have focused on the TO_DATE function of PostgreSQL in Windows 10 in this article. To introduce you to this function, we first talked briefly about it by discussing its syntax and other relevant details. After that, we discussed five different examples that included various queries incorporating the TO_DATE function of PostgreSQL in Windows 10. After going through all of these examples, you will learn quite well how the TO_DATE function works in PostgreSQL in Windows 10.

About the author

Aqsa Yasin

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.