Full date conversion
-
Got this little gem while rewriting the sql statements for usage in our own database:
to_date(to_number(to_char(to_date(@Prompt('3. End date','D',,MONO,)),'yyyymmdd')),'yyyymmdd') + 1The owner of this code forgot that the @prompt command can automatically convert the prompt output to a date.
-
I think what gets me is converting the date to a string, then to a number, and finally back to a date, all for absolutely no reason. I could understand if he went straight to number and adding the one there, indicating he doesn't understand how databases handle dates.
-
From what I know about Oracle this may as well be recommended practice.
-
@ArrivingRaptor said:
I think what gets me is converting the date to a string, then to a number, and finally back to a date, all for absolutely no reason. I could understand if he went straight to number and adding the one there, indicating he doesn't understand how databases handle dates.
It does seem like overkill. In MS SQL I have a function that converts a DateTime to float, floors it and converts it back to a date. That is because SQL DateTimes are realy just numbers, with the fractional portion representing the time. Conversion like this to get just the date without the time was faster than converting to a string and parsing it back to a date. Unfortunately, SQL doesn't (unless they added it recently) have a function to just get the date part of a DateTime easily.
-
@The Bytemaster said:
@ArrivingRaptor said:
I think what gets me is converting the date to a string, then to a number, and finally back to a date, all for absolutely no reason. I could understand if he went straight to number and adding the one there, indicating he doesn't understand how databases handle dates.
It does seem like overkill. In MS SQL I have a function that converts a DateTime to float, floors it and converts it back to a date. That is because SQL DateTimes are realy just numbers, with the fractional portion representing the time. Conversion like this to get just the date without the time was faster than converting to a string and parsing it back to a date. Unfortunately, SQL doesn't (unless they added it recently) have a function to just get the date part of a DateTime easily.
MS SQL also has a date object that you could convert to (added in 2008)
-
@The Bytemaster said:
Unfortunately, SQL doesn't (unless they added it recently) have a function to just get the date part of a DateTime easily.
It was extremely long in coming, but they finally have a "Date" datatype. (And a "Time" datatype, but that's less useful.
So to get just the date part of a DateTime, just cast it to Date. Simple.
