Home » Programming » How to convert String to Date in SQL statement?

How to convert String to Date in SQL statement?

24 December 2008
Goto comments Leave a comment Print This Post

This is a list of handy tips that could help you in a blink on how to convert String to Date in SQL. This post cover 5 popular databases (Oracle, MySQL, DB2, PostgreSQL, Informix)

Post your comment here if you find this post useful or want to contribute more explaination.

Oracle

Syntax:

to_date(date_string, format)

Example:

SELECT user.name, user.location FROM user
WHERE user.starteddtm = to_date('20/10/2007 16:01', 'dd/mm/yyyy hh24:mi');

(more details on this function)

MySQL

Syntax:

STR_TO_DATE(date_string, format)

Example:

SELECT user.name, user.location FROM user
WHERE user.starteddtm = STR_TO_DATE('20/10/2007 16:01', '%d/%m/%Y %H:%i');

(see more details on date format)

DB2

In DB2 You can use the following function to convert a character string to a date or time value

Syntax:

TIMESTAMP(date_time_string)
DATE(date_string)
TIME(time_string)

Example:

TIMESTAMP ('2002-10-20-12.00.00.000000')
TIMESTAMP ('2002-10-20 12:00:00')
DATE ('2002-10-20')
DATE ('10/20/2002')
TIME ('12:00:00')
TIME ('12.00.00')
SELECT user.name, user.location FROM user
WHERE user.starteddtm = DATE('20/10/2007');

(more details)

PostgreSQL

Syntax:

to_date(date_string, format)

Example:

SELECT user.name, user.location FROM user
WHERE user.starteddtm = STR_TO_DATE('20/10/2007 16:01', 'DD/MM/YYYY HH24:MI');

(more details)

Informix

Syntax:

DATE(date_string)
TO_DATE(date_string, format)

Example:

SELECT user.name, user.location FROM user
WHERE user.starteddtm = DATE('20/10/2007');
SELECT user.name, user.location FROM user
WHERE user.starteddtm = TO_DATE('20/10/2007 16:00', '%d/%m/%Y %H:%M');

(more detail on these functions)

Author: Manet Yim Programming

2,171 views
  1. No comments yet.
  1. No trackbacks yet.