Page 1 of 1
Timestamp conversion in SQL Server
Posted: Tue Aug 08, 2006 4:10 pm
by ra9
we are using Parallel, Windows, Sql server 2005
we are getting like this in Flat files
7/6/2004 5:00:00 AM
8/15/2005 4:59:00 AM
11/21/2005 4:22:22 AM
Target is sql server using datatype TIMESTAMP.
need to be converting into
2004-07-06 05:00:00.000
2005-08-15 04:59:00:000
2005-11-21 04:22:22:000
if any one have idea about this , please could let me know.
Thanks
ra9
Posted: Tue Aug 08, 2006 4:41 pm
by kris007
Did you try doing it normally? Did you face any errors while loading the data as it is? All you need to do is set the datatype to Timestamp with a scale of 3. Try doing that and see if it works. If it doesnt, then post the error message so that it will be easier for us to answer.
Posted: Tue Aug 08, 2006 5:14 pm
by lstsaur
ra9,
I was told by Ascential that 7.5X2 EE does not support SQL 2500.
We do need some more information to answer this
Posted: Tue Aug 08, 2006 6:44 pm
by jdmiceli
Hi,
I think 'lstsaur' is right that SQL Server 2005 is not directly supported yet, but the ODBC driver may work provided you have the most recent MDAC loaded (though this is speculation on my part).
If the other suggestion made ahead of my note here don't work for you, try using a user defined sequel that contains the key term 'COLLATE DATABASE_DEFAULT' for the date field after performing a CAST on the field name. Something similar to CAST(fieldname as varchar(20)). Bear in mind, I'm pulling this off the top of my head and I don't know if there needs to be any modification for 2005 or not. Hit the online books for the SQL side of it.
Overall, I agree with the others that we really need more information about your job to be able to truly help.
Bestest,
Re: Timestamp conversion in SQL Server
Posted: Thu Mar 15, 2007 1:05 pm
by shilpa79
ra9 wrote:we are using Parallel, Windows, Sql server 2005
we are getting like this in Flat files
7/6/2004 5:00:00 AM
8/15/2005 4:59:00 AM
11/21/2005 4:22:22 AM
Target is sql server using datatype TIMESTAMP.
need to be converting into
2004-07-06 05:00:00.000
2005-08-15 04:59:00:000
2005-11-21 04:22:22:000
if any one have idea about this , please could let me know.
Thanks
ra9
In my case I have to insert date "8/15/2005 4:59:00 AM "from XML file
same into the Sql server 2005
Input sqltype varchar---------------> output Timestamp