Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2011
    Posts
    82

    Case when statement with text and max date

    Hello,

    Could someone kindly help me rewrite the correct syntax in order to use a date and text field in a case statement. However I need to find the maximum date of the date field, so am assuming that I need to to use both to_char and to_date

    case when location = 'DOSPR' then Max(TO_CHAR(TO_DATE(datetime,'YY/MM/DD'),' YY/MM/DD')) else '' end as test,

    Can anyone help?

    Thanks
    Helen

  2. #2
    Join Date
    Mar 2007
    Posts
    615
    Hello,

    Unfortunately, it is not clear from your post, what is the data type of the DATETIME column.

    If it is CHAR/VARCHAR2 in 'YY/MM/DD' format (to which leads the usage of conversion functions), then they have no effect except eventual failure for some values representing wrong (e.g. non-existent) date.
    If it is DATE, then (as TO_DATE does not support DATE parameter) DATETIME is implicitly converted to VARCHAR2 with NLS_DATE_FORMAT format mask which will lead to undeterministic behaviour with possibilty of failure when NLS_DATE_FORMAT is set to non-matched value.

    In both cases (as 'YY/MM/DD' format is ordered by its DATE representation), why not simply use MAX( DATETIME )?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •