CODEWITHSUNDEEP
oracle-databasedateformat
Solid-Snake
Solid-Snake

Reputation: 147

Convert a date with Oracle

I would like to convert a date with Oracle, from this format:

25/01/2016 (DD/MM/YYYY)

To this format:

2016-01-25 (YYYY-MM-DD)

Here is the request this is required for (from this earlier question):

  WITH bounds AS (
      SELECT to_char(to_date('01/01/2016','DD/MM/YYYY'), 'YYYY-MM-DD') AS lower_bound,
             to_char(to_date('29/02/2016','DD/MM/YYYY'), 'YYYY-MM-DD') AS upper_bound
      FROM   DUAL
    ),
    weeks AS (
      SELECT LEVEL AS id,
             lower_bound + (LEVEL - 1) * 7 AS week_date
      FROM   bounds
      CONNECT BY lower_bound + (LEVEL - 1) * 7 <= upper_bound
    )
    SELECT 'W' || TO_CHAR( week_date, 'WW' ) AS week
    FROM   weeks;

This works:

to_date(to_char(to_date('".$dateDebut."','DD/MM/YYYY'), 'YYYY-MM-DD'), 'YYYY-MM-DD')

Upvotes: 0

Views: 644

Answers (2)

Alex Poole
Alex Poole

Reputation: 191235

You are only really trying to convert a string to a date; as you're using it in a query it needs to stay as a date, and an Oracle date has no intrinsic format (it has an internal representation you don't care about here).

Your proposed solution:

to_date(to_char(to_date('".$dateDebut."','DD/MM/YYYY'), 'YYYY-MM-DD'), 'YYYY-MM-DD')

is converting your string to a date using one format mask; then back to a string and back to a date again using a different format mask. This is redundant, you only need:

to_date('".$dateDebut."','DD/MM/YYYY')

So you query becomes:

WITH bounds AS (
  SELECT to_date(".$dateDebut.",'DD/MM/YYYY') AS lower_bound,
         to_date(".$dateFinis.",'DD/MM/YYYY') AS upper_bound
  FROM   DUAL
),
weeks AS (
  SELECT LEVEL AS id,
         lower_bound + (LEVEL - 1) * 7 AS week_date
  FROM   bounds
  CONNECT BY lower_bound + (LEVEL - 1) * 7 <= upper_bound
)
SELECT 'W' || TO_CHAR( week_date, 'WW' ) AS week
FROM   weeks;

Embedding string values from your UI into a query is bad practice though, as it leaves you open to SQL injection attacks. Look into bind variables to avoid this.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

One method uses to_date() and to_char():

select to_char(to_date(col, 'DD/MM/YYYY'), 'YYYY-MM-DD')

You should, however, be storing dates in the database using native date formats, rather than as strings.

You can also use string manipulation:

select substr(col, 7, 4) || '-' || substr(col, 4, 2) || '-' || substr(col, 1, 2)

Upvotes: 1

Related Questions