September 7, 2007 at 4:03 pm
Hi all. I am exporting shipment data from a UPS system to an internal SQL Server 2005 table. I don't have control over the data format being exported. I was trying to export their collection date to my datetime field. UPS stores the date as a string value in 20070905150836 format. The export fails with a conversion error. I can export into a varchar type field but need to evaluate the date field on our tracking website. What would be the best method to get this data in a datetime field on insert? I haven't used triggers but was thinking this might be a solution??
September 7, 2007 at 4:08 pm
You could either user triggers or calculated columns. If your data is comming through bcp a calculated column maybe the way to go otherwise triggers are ok, if you are writting a front end program to do the import the format could better be done there instead.
Cheers,
* Noel
September 7, 2007 at 4:18 pm
This is actually an export from the UPS WorldShip software. It exports data directly to one of my tables using an ODBC connection. I tried a computed column using CONVERT(datetime,SUBSTRING(CollectionDate,1,8)) as the formula but get an error. I also tried using just the SUBSTRING(CollectionDate,1,8) portion which doesn't error but I'm not sure I can evaluate the computed column as a date. Any thoughts?
September 10, 2007 at 7:30 am
If you have seen that UPS WorldShip always puts their dates in that format with yyyymmddhhmmss then you could use the formula below to cast that date/time into your date/time format. A little lengthy, but it does the conversion.
declare
@dt varchar(15)
set
@dt='20070905150836'
declare
@date smalldatetime
set
@date=cast(substring(@dt,1,4)+'-'+substring(@dt,5,2)+'-'+substring(@dt,7,2)+' '+substring(@dt,9,2)+':'+substring(@dt,11,2)+':'+substring(@dt,13,2) as smalldatetime)
@date
September 10, 2007 at 9:10 am
Would something like the post above work in a trigger?
September 12, 2007 at 6:27 am
When I was working with UPS to update our database with the tracking number, I wasn't converting the date at the time, but I created a table for UPS to insert into and had a trigger on that table to update the proper table in our database. Absolutely you could do the above formula in a trigger in that case.
September 12, 2007 at 9:17 am
Thank you for the posts. I'll give it a whirl this afternoon. It high time I get started with triggers.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply