| Author |
Topic |
|
ciaron
Starting Member
5 Posts |
 Posted - 2010-01-05 : 06:22:09
|
| Hi,Am using the bulkimport command to import a CSV file into a table. No problems there. However the dates in the csv file are stored in ISO standard (eg yyyymmdd) format. I would for various reasons like them to be stored in the sql file in mm/dd/yyyy format. Anyone know if it is possible to import and convert these? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-05 : 06:25:53
|
In SQL you should use the right data type in the table (date or datetime) and it is not your problem how SQL Server stores the format.By selecting the data or by showing it in front end you can then convert it to the wanted format.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
ciaron
Starting Member
5 Posts |
Posted - 2010-01-05 : 06:30:46
|
| ok think I got that (sorry pretty new to SQL) so I need to set that entry in the table to yyyymmdd format? How do I choose the format of the date field when I create a table though?So using the following as an exampleCREATE TABLE CVDOUT(ID INT,FirstName VARCHAR(40),LastName VARCHAR(40),BirthDate DATE);how do I choose the date format of the birthdate entry?PS - many thanks in advance! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-01-05 : 06:52:37
|
CONVERT(VARCHAR(10), BirthDate, 101)CONVERT(VARCHAR(10), BirthDate, 112)
N 56°04'39.26"E 12°55'05.63" |
|
|
|
ciaron
Starting Member
5 Posts |
Posted - 2010-01-05 : 08:21:02
|
| Is there a list anyone can point to of what the format codes are? Am assuming thats what the 101 and 112 are?Assume that I therefore import the dates as text strings and then use the convert function to change them to dates?Sorry if I am being slow here. |
|
|
|
ciaron
Starting Member
5 Posts |
Posted - 2010-01-05 : 08:22:33
|
| Or does it now store the date as text string? Only ask as need to do some calculations based on date as well. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-05 : 08:33:17
|
The table with the codes to use in convert:http://msdn.microsoft.com/en-us/library/ms187928.aspxIf your import-date comes as '20100105' and the destination column is a date column then the date will be converted automatic.Don't worry about the format sql is storing the date in the table.You can use the convert (like Peso has shown) when you are retrieving data from a table and want to show it in a wanted format.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
ciaron
Starting Member
5 Posts |
Posted - 2010-01-05 : 08:41:57
|
| ahhh gotcha, yeah I have defined the entry on the table as a date table, do I need to define the date format on the table as yyyymmdd at all? Will just use the convert for display stuff which is cool. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-05 : 08:52:56
|
quote:
do I need to define the date format on the table as yyyymmdd at all?
No. You define the column as a type DATE. SQL Server then knows what to do...What I meant was: If your date, coming from import-file, is in format 'yyyymmdd' then SQL Server is always sure and has no problems to take this value and store it in it's own dateformat.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
CSV files and ISO dates