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 |
|
Grifter
Constraint Violating Yak Guru
274 Posts |
 Posted - 2012-05-10 : 06:41:49
|
| HiIn one of our DBs we ended up with imported dates from another system and I basically need to format these so they are all the same. The system they came from was changed frequently and we had a lot of trouble because they were sending us strings with dates formatted all over the place.I now have these 3 distinct formats in a column:nulldd/mm/yyyymmm dd yyyy 12:00AMAnd I want to change the last date/time format one to dd/mm/yyyy, and also is there a way to check these strings for data that would not normally be in a date format such as dd/mm/yyyy*, so I would be able to recognise only the data and clear out any rubbish in the string.Don't ask why the imported values were not from date fields, I asked the developer and they said it made it easier for them to import them from the sales system!! however a nightmare on our side as we keep running into date formatting problems as we try to fix the strings into a proper date format.G |
|
|
Grifter
Constraint Violating Yak Guru
274 Posts |
Posted - 2012-05-10 : 07:01:39
|
This worked for me but how do I then convert to a proper format:select distinct CONVERT(DATE, MyDatefield, 103) AS MyDatefieldfrom all_data As in my returned dates are now yyyy-MM-ddDo I need to convert back to string and format as dd/mm/yyyy??G |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2012-05-10 : 07:04:17
|
| select convert(varchar(10),cast('jan 16 2007 12:00AM' as datetime) ,103)But Import data into staging table and move data to source table by converting VARCHARs to DATETIME and use column with DATETIME datatypeMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
Date Formatting