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 |
|
bendertez
Yak Posting Veteran
94 Posts |
 Posted - 2007-10-11 : 04:55:44
|
| HiI have just imported some external data into a database and have found that the date format is different for one of the imported date fields.The date goes in OK as it is a VARCHAR(10) field but the format for the inserted records is mm-dd-yy, what I really want is the format to be dd/mm/yy.There must be a way of updating the whole field to the desired format.I have tried this:update table set datefield = (convert(varchar,datefield,103))which runs OK but seems to leave it in the same imported format.Any ideas?Thanks |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-11 : 04:58:41
|
UPDATE Table1 SET Col1 = CONVERT(CHAR(8), CONVERT(DATETIME, Col1, 101), 103)
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-11 : 04:59:11
|
Why are you not using PROPER datatype (DATETIME or SMALLDATETIME) for this column?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
bendertez
Yak Posting Veteran
94 Posts |
Posted - 2007-10-11 : 05:38:07
|
| Thanks PesoThis particular table has been set up on the instruction of consultants who are developing an AD interface.The original date field in the database is in a DATETIME format but for some unknown reason they want the same field refected in their table in a VARCHAR format..... I asked the same question when they requested this and was told that it had to be VARCHAR for their AD interface...!!This field has to be in VARCHAR format and not DATETIME, I presume i'll have to figure out a way using substring/instring to manipulate the format..??Thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-11 : 06:49:54
|
| If it has to be "reflected" you could have two columns, varchar & datetime, and use a trigger to keep them synchronised.Kristen |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-10-11 : 07:18:50
|
| I would seriously recommend hiring new consultants ... sounds like they have no clue about the most basic database concepts... good luck!- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
|
|
|
Date format within a table