| Author |
Topic |
|
sirmystic
Starting Member
4 Posts |
 Posted - 2009-01-25 : 20:49:11
|
| So I am doing a fairly straight forward database upgrade from access 97 to SQL08. I have everything setup correctly except one thing. In transferring the tables from the database to SQL I am getting an error saying invalid date format. Simple enough, as when I looked, some of the dates are in the format x/x/xxxx. I'm sure its straight forawrd to the rest for you, but in the the SQL import wizard I can use a SQL statement to import this format and manipulate it so it is in the right format.I am completely new to SQL so I'll post any details I think may help you come up with the statement. It the "client" table there are a couple different dates that need to be imported. For example, one client record has among other things a "DOB", "Spouse DOB", "Date Updated" If you can help me with the entire statement, I don't have a clue where to start. If I can answer any other questions I will. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-01-25 : 21:04:25
|
| This is usually a good time to redesign the database.Just reformat the date to yyyymmdd or yyyymmdd hh:mm:ss.mmmI usually import into a character column then reformat.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
sirmystic
Starting Member
4 Posts |
Posted - 2009-01-25 : 21:10:06
|
| We are going to redesign everything after we get them migrated. Temporarily we are going to use their old access front end to get them up and running, then redisign everything. I have a friend who wasn going to work through the whole project with me however having a hard time getting ahold of him. Can you help me with the actual SQL statement to enter into the Source Query box? If I get the last 3 tables imported the databse will be operational. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-01-25 : 21:13:49
|
| It would need an access sql statement to do it in the transfer.As I said it's usually easier to transfer to a character column then do the convert in t-sql.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
sirmystic
Starting Member
4 Posts |
Posted - 2009-01-25 : 21:21:24
|
| Ok, I'll import the tables with the dates set as plain text. Then there is just a simple sql command to run on the table after its in SQL to change it to the right date format? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-01-25 : 21:38:11
|
| Copy from that table into the production table.The command will depend on the date format you end up with.If you post that I (or someone else) will post the statement.Make sure that the dates are consistent - i.e. those before the 13th of the month are converted the same way as those after.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
sirmystic
Starting Member
4 Posts |
Posted - 2009-01-25 : 21:49:32
|
| The current format is m/d/yyyy. I think in order for SQL to consider it a date it has to be in the format mm/dd/yyyy. mm/dd/yyyy would be perfect |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-01-25 : 22:10:24
|
| Nope - that would depend on the localle. you need yyyymmdd.so something likeright(col,4) + right('0' + left(col,charindex('/',col)-1),2) + right('0' + replace(substring(col,charindex('/',col)+1,2),'/',''),2)==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
Transfer from Access 97 to SQL08