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 |
|
Crespo24
Village Idiot
144 Posts |
 Posted - 2003-04-07 : 06:48:54
|
I often receieve data that is badly formatted.One of the most common ones is dates in the format of d/m/y, or 2/11/1998 etc...What I normally do is convert the date so that it has the necessary two digits so the above date becomes 02/11/1998 or 4/5/1993 bceomes 04/05/1993.I use the following simple updates :UPDATE tableSET date = '0'+dateWHERE CHARINDEX('/', date) = 2GOUPDATE tableSET date = SUBSTRING(date, 1, 3)+'0'+SUBSTRING(date, CHARINDEX('/', date)+1, 6)WHERE CHARINDEX('/', date, CHARINDEX('/', date)+1) = 5GOI have two questions:1) is there a smarter or better update instead of the above?2) does SQL server have any sort of function that deals with such date formats?Thanks in advance. |
|
|
samsekar
Constraint Violating Yak Guru
437 Posts |
Posted - 2003-04-07 : 07:15:17
|
Welcome back Crespo..  I don't think there is a need of first update statement.bcoz. select isdate('4/5/1993'), isdate('04/05/1993') returns 1 for both value..Second.. for insering you can set dateformat to dmy.. (SET DATEFORMAT dmy) and insert the records as it is. Hence no need for second update statement.EDIT: I thought you would have unsubscribed by now.. Sekar~~~~Success is not a destination that you ever reach. Success is the quality of your journey.Edited by - samsekar on 04/07/2003 07:16:28 |
 |
|
|
Crespo24
Village Idiot
144 Posts |
Posted - 2003-04-07 : 08:16:29
|
quote:
Welcome back Crespo.. I don't think there is a need of first update statement.bcoz. select isdate('4/5/1993'), isdate('04/05/1993') returns 1 for both value..Second.. for insering you can set dateformat to dmy.. (SET DATEFORMAT dmy) and insert the records as it is. Hence no need for second update statement.EDIT: I thought you would have unsubscribed by now.. Sekar~~~~Success is not a destination that you ever reach. Success is the quality of your journey.Edited by - samsekar on 04/07/2003 07:16:28
I had a rough patch and now I feel fine. (I don't know about you, but having family members in Iraq at the moment makes me feel a bit upset). This is actually me minus my adopted personality. So I am not really back.... but then again you don't know me!Thanks for your answer though. I guess my SQL knowledge isn't that good because I thought that the DATEFORMAT command was only used to set the order of the digts. I've been converting all my dates to have two digits per date part!Thanks again.------------------------Who am I going to be today? |
|
|
|
samsekar
Constraint Violating Yak Guru
437 Posts |
Posted - 2003-04-07 : 08:47:37
|
| I feel extremely sorry for that crespo.I agree that "war for peace is like fu..... for virginity". I don't want to talk about that here and spoil the forum's discipline.Sekar~~~~Success is not a destination that you ever reach. Success is the quality of your journey.Edited by - samsekar on 04/07/2003 08:53:17 |
|
|
|
Crespo24
Village Idiot
144 Posts |
Posted - 2003-04-07 : 08:52:52
|
quote:
I feel extremely sorry for that crespo.I agree that "war for peace is like fucking for virginity". I don't want to talk about that here and spoil the forum's.Sekar~~~~Success is not a destination that you ever reach. Success is the quality of your journey.
Thanks for that... I do appreciate your concern. Let's hope it's over soon. All I can say is THANK GOD FOR THE BRITISH!------------------------Who am I going to be today? |
|
|
|
|
|
|
|
|
Date format