Date format - need help!

Hi Guys,
Need some help.
I have a sql script generated in oracle consisting of insert into values and
I'm trying to run that script in SQL Server 2k, but it has a TO_Date
function and I'm unable to run it. I looked on the web for a work around and
didn't come up with anything.
below is a sample script which I'm trying to run in SQL Server. Thanks.
INSERT INTO FOLDER ( FLDR_ID, PARENT_ID, COMPANY_ID, FLDR_NAME, ATTEND_NAME,
BIRTH_DATE,
MATRIC_DATE, GENDER, CREATE_DT, USER_ID, STAT_ID, UPDT_DT, PRIVACY_LOCK,
SECURITY_LVL ) VALUES (
504210, 2031806529, 1, '361384843', LINDA KAY ', CAST( '03/09/1992 12:00:00
AM', 'MM/DD/YYYY HH:MI:SS AM')
, CAST ( '05/14/1990 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'M', CAST(
'04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 79807613, 9, CAST( '04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS AM'),
0, 0);oops sorry, I copied the script I was working around, here is the original
script.
INSERT INTO FOLDER ( FLDR_ID, PARENT_ID, COMPANY_ID, FLDR_NAME, ATTEND_NAME,
BIRTH_DATE,
MATRIC_DATE, GENDER, CREATE_DT, USER_ID, STAT_ID, UPDT_DT, PRIVACY_LOCK,
SECURITY_LVL ) VALUES (
504210, 2031806529, 1, '361384843', 'LINDA KAY', TO_Date( '03/09/1952
12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '05/14/1990 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'M',
TO_Date( '04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 79807613, 9, TO_Date( '04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS
AM'), 0, 0);
"CoolHandSid" <sid@.handluke.com> wrote in message
news:eca5bMpGFHA.2276@.TK2MSFTNGP15.phx.gbl...
> Hi Guys,
> Need some help.
> I have a sql script generated in oracle consisting of insert into values
and
> I'm trying to run that script in SQL Server 2k, but it has a TO_Date
> function and I'm unable to run it. I looked on the web for a work around
and
> didn't come up with anything.
> below is a sample script which I'm trying to run in SQL Server. Thanks.
> INSERT INTO FOLDER ( FLDR_ID, PARENT_ID, COMPANY_ID, FLDR_NAME,
ATTEND_NAME,
> BIRTH_DATE,
> MATRIC_DATE, GENDER, CREATE_DT, USER_ID, STAT_ID, UPDT_DT, PRIVACY_LOCK,
> SECURITY_LVL ) VALUES (
> 504210, 2031806529, 1, '361384843', LINDA KAY ', CAST( '03/09/1992
12:00:00
> AM', 'MM/DD/YYYY HH:MI:SS AM')
> , CAST ( '05/14/1990 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'M', CAST(
> '04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS AM')
> , 79807613, 9, CAST( '04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS AM'),
> 0, 0);
>
>|||Use one of the following forms for dates.
'20050131' -- Just the date
'2005-01-31T17:59:00' -- Date/hours/minutes/seconds
'2005-01-31T17:59:00.000' -- Date/hours/minutes/seconds/milliseconds
Note that the 'T' is required with the date and time forms otherwise
the result is sensitive to regional date format settings.
David Portas
SQL Server MVP
--|||David,
Thanks for the reply.
The problem is I have a file with 800,000 lines with TO_Date which was
geneated from a oracle DBA. I'm trying to get that into a table in SQL
Server. Can you suggest some user defined function so I can run it faster?
Thanks.
Again the sample script is as follows,
INSERT INTO FOLDER ( FLDR_ID, PARENT_ID, COMPANY_ID, FLDR_NAME, ATTEND_NAME,
BIRTH_DATE,
MATRIC_DATE, GENDER, CREATE_DT, USER_ID, STAT_ID, UPDT_DT, PRIVACY_LOCK,
SECURITY_LVL ) VALUES (
504210, 2031806529, 1, '361384843', 'LINDA KAY ', TO_Date( '03/09/1952
12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '05/14/1990 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'M',
TO_Date( '04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 79807613, 9, TO_Date( '04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS
AM'), 0, 0);
--
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109265282.722910.17120@.f14g2000cwb.googlegroups.com...
> Use one of the following forms for dates.
> '20050131' -- Just the date
> '2005-01-31T17:59:00' -- Date/hours/minutes/seconds
> '2005-01-31T17:59:00.000' -- Date/hours/minutes/seconds/milliseconds
> Note that the 'T' is required with the date and time forms otherwise
> the result is sensitive to regional date format settings.
> --
> David Portas
> SQL Server MVP
> --
>|||Not fully tested but it seems to work with what you posted.
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
You'll need to search and replace "TO_DATE" with "dbo.to_date" first.
David Portas
SQL Server MVP
--|||David,
Thanks a lot, that worked like a charm.
Thanks!!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109268237.382512.109560@.o13g2000cwo.googlegroups.com...
> Not fully tested but it seems to work with what you posted.
> 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
> You'll need to search and replace "TO_DATE" with "dbo.to_date" first.
> --
> David Portas
> SQL Server MVP
> --
>