September 30, 2008 at 12:04 pm
I am trying to write a function to make an old Oracle app more cross platform. Is seems to work for the most part, except when certain date types are passed in. I cannot for the life of me figure out which of the values on a certain line (being concatenated) is represented as an int. I have tried removing the comma, to no avail.
Here is the input to the function:
select dbo.to_char(insert_datetime,'HH24:MI:SS, MM-DD-YY') from test_table
Here is the function:
alter function to_char(@TS datetime, @fmt varchar(50))
returns varchar(50)
AS BEGIN
RETURN
CASE @fmt
when 'yyyymmdd'
then CONVERT(VARCHAR(8), @TS, 112)
when 'HH24:MI:SS, MM-DD-YY'
then CONVERT(VARCHAR(8), @TS, 8) + ', ' + CONVERT(VARCHAR(8), @TS, 101)
else
'date format ' + coalesce(@fmt,' ') + ' not recognized.'
END
END
It seems to be hanging up on that second CONVERT line. Any ideas?
September 30, 2008 at 12:12 pm
Some sample data, a table for the sample data, and the expected output would help greatly. Preferable, all of this should be done such that all we have to do is cut and paste into SSMS and run.
😎
September 30, 2008 at 12:51 pm
Thanks for the comment. I actually realized (while putting together what you asked for) that an older version of the function was active, not what I pasted. The older function had this:
when 'YYYY'
then DATEPART(yyyy, @TS)
I did not realize DATEPART returned an integer. I added CAST statements where necessary and I am good to go now. Thanks!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply