1

Just trying to clean up some functions someone else has done in a postgres.

Would anyone know what the following does? It was working, but the date format changed when it started coming in as '1999-09-07 16:30:00.000'

I don't know what the previous format was.

select 
case 
    when dbDate = '' then null
    when dbDate != '^\d.*' then dbDate::timestamp
    else '1900-01-01'::date + dbDate::int   
end as dbDate

Whenever I call the function with the date it gives me

invalid input syntax for integer: "1999-09-07 16:30:00.000"

emraldinho
  • 475
  • 8
  • 23

1 Answers1

2

This nice function was taking multiple kinds of date inputs and normalizing them.

I assume it expected one of:

  • blank which it let be null
  • a date starting with 'MMM' in date format which would not pass '^\d.*' (i.e. something that doesn’t start with a number) which it would cast as a date
  • a number

The reason that the date was being casted as an INT is because after failing the first two tests the person writing this was expecting an INT. They wanted to add the integer to the beginning of Time (i.e. 1900-01-01) like Excel does.

1999-09-07 16:30:00.000 fails the second test even though it could be cast as time.

This passes through to the else, which fails to cast it as INT, and throws the error.

In this case, you need to change your second test. Make it something that will allow a datetime that you have coming in, but that would reject a number that should be added to 1900-01-01.

If you don’t think you will have numbers coming in that should be added to 1900-01-01, then just get rid of the third test and use

select 
case 
    when dbDate = '' then null
    else dbDate::timestamp
end as dbDate
Toby Speight
  • 27,591
  • 48
  • 66
  • 103
EoinS
  • 5,405
  • 1
  • 19
  • 32
  • 1
    would the replace command be: replace dateconverter select case when dbDate = '' then null else dbDate::timestamp end as dbDate – emraldinho May 17 '16 at 03:58
  • Honestly I think it depends on what the previous format was and what kind of data you typically get on dbdate field. Run a query to pull all unique dbdates and check it out. If you see a lot of integers that make sense when added to the old date format you may need to make some changes to the function to allow for this data. If they are all datetime or zls you could use the above. – EoinS May 17 '16 at 04:07
  • Did this solution work for you? If you are selecting INTs rather than Dates and getting some strange Datetimes, we can work on a way to accept the incoming INTs and change them to dates as the Function initially did. – EoinS May 17 '16 at 16:56