Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
David2455
Starting Member
6 Posts |
 Posted - 2011-04-27 : 08:48:47
|
| Hi,I am using sql express so cannot use any DTS tools. I have a csv file to import, which includes several dates. each column in the csv is seperated by a comma and enclosed in speech marks, i.e "," I cannot change this. I import the csv into a temporary table made up of varchar columns and remove the "," seperators and row leading/closing ". This works fine, however the first date column changes from dd/mm/yyyy to dd/mm/yy, though all other columns come through as dd/mm/yyyy, albeit varchar.I get errors when I try to import the temporary table data to the correctly formatted datetime columns "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value" across all date columns yy and yyyy. I have tried a convert(varcher(50),[column1],6) as [column1] to specify the format, but that doesn't work.Any assistance would be very welcome as I'm running out of ideas (time and patience)! |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-04-27 : 08:56:26
|
| >> the first date column changes from dd/mm/yyyy to dd/mm/yyAre you importing into a varchar column? If not then do that and the format won't be changed by the import.>> convert(varcher(50),[column1],6)try convert(datetime,[column1],103) or convert(datetime,[column1],3)if this is a varchar column (I suspect it is).==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
David2455
Starting Member
6 Posts |
Posted - 2011-04-27 : 09:19:02
|
| Thanks for the help. I have run your suggestion and get the following;column1 now shows date in correct format, along with other columns. However its still a varchar and I can't perform calculations. I have tried to insert to the permannet table (with datetime columns), but get the error message "Conversion failed when converting date and/or time from character string" and the first date returns to dd/mm/yy? |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-04-27 : 09:58:57
|
| I'm not sure why you keep saying the date is reverting to a format - it sounds as though you are updating your staging table instead of inserting into the destination.tryselect max(convert(datetime,[column1],103))from tblThat will show whether you have an invalid date there.If not you can use the same code for the insertinsert dest (...)select convert(datetime,[column1],103), ...from tbl==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
David2455
Starting Member
6 Posts |
Posted - 2011-04-27 : 13:28:16
|
| Hi againYes that was the problem (updating the staging table, not the main table). Thank you very much for the assistance and the swift responses, I'd been going round in circles for ages. |
|
|
|
FlyOnTheWall
Starting Member
1 Post |
Posted - 2011-05-30 : 01:55:05
|
| I understand how to apply the type conversions but wondering where it is that I put this in teh IMPORT wizard or is it not available here? I also saved the DTS and opened it but still unsure? Any advice really appreciated. Thanks |
|
|
|
|
|
|
|
|
Date format problems on Import