CODEWITHSUNDEEP
sqloracle-databasedatevarchar
user1895076
user1895076

Reputation: 749

Convert Varchar with Nulls to Date in Oracle SQL

I have a table with a VARCHAR column of dates in the following format: 2005-02-17 00:00:00. Some of the rows contain a date such as the one listed above, and the others are NULL.

How can I convert the whole column to the DATE format so that I can compare with other dates. My end goal is to create a flag that is 1 if the date in my VARCHAR column is between 1-1-2014 and 3-31-2014 (i.e. between to_date('01/01/2014','mm/dd/yyyy') and to_date('03/31/2014','mm/dd/yyyy')).

Thanks.

Upvotes: 1

Views: 1982

Answers (2)

Paddy Carroll
Paddy Carroll

Reputation: 538

simplest approach create a view with

SELECT  NVL(my_dates, '1900-01-01 00:00:00')

and operate on that

Upvotes: 0

Mureinik
Mureinik

Reputation: 311188

Calling to_date on a null value will just return null, which ins't between any two values, so you can just ignore this case:

SELECT CASE WHEN 
              TO_DATE(my_varchar, 'yyyy-mm-dd hh24:mi:ss') 
              BETWEEN TO_DATE('01/01/2014','mm/dd/yyyy') AND 
                       TO_DATE('03/31/2014 23:59:59','mm/dd/yyyy hh24:mi:ss')
            THEN 1
            ELSE 0
        END AS flag
FROM    my_table

Upvotes: 1

Related Questions