Monday, March 12, 2012

Function update

A gentleman from here helped me to write a function to convert oracle
date to a MS Sql date.
Here is the link
http://groups.google.com/group/micr...r />
8Jm9ADFUU
Yr6IuaVp8r6Fxhin3IJmS3764Q
The previous function you wrote
CREATE FUNCTION dbo.to_date
(@.dt VARCHAR(50), @.dt_format VARCHAR(50))
RETURNS DATETIME
AS
BEGIN
RETURN
CONVERT(DATETIME,
CONVERT(CHAR(12),
CAST(
SUBSTRING(@.dt,PATINDEX('%YYYY%',@.dt_form
at),4)+
SUBSTRING(@.dt,PATINDEX('%MM%',@.dt_format
),2)+
SUBSTRING(@.dt,PATINDEX('%DD%',@.dt_format
),2) AS DATETIME)
,0)+
SUBSTRING(@.dt,PATINDEX('%HH%',@.dt_format
),2)+':'+
SUBSTRING(@.dt,PATINDEX('%MI%',@.dt_format
),2)+':'+
SUBSTRING(@.dt,PATINDEX('%SS%',@.dt_format
),2)+' '+
SUBSTRING(@.dt,PATINDEX('%AM%',@.dt_format
),2),9)
END
works for TO_Date( '12/27/1996 03:48:26 PM', 'MM/DD/YYYY HH:MI:SS AM')
I would like to modify your function for the data below
to_date('05/31/2000 10:17:22', 'MM/DD/YYYY HH24:MI:SS')
Can you help me to modify the function to work with the new format.
Thanks.Thanks. I figured it out. simple solution to just Return
convert(datetime,@.dt) without all the complexity.

No comments:

Post a Comment