June 22, 2011 at 2:26 am
Hello,
I have a table that contains a varchar column where certain dates are saved in different formats (23 MAY 1946; 1880; DEC 1793; 31 1836; 11 1712; 16541667; 1920-1929; 1910-03-04; 091707; 03.10.1850; 1761////; 1618 OU 1621; 1728-12; 18.09.1677; 1622/1623; 1860- - [10/622]; 1864/07/09 etc.)
What I need to do is find every record that can be identified as a date and has the format for month numeric and then, change the month from numeric to the name. (05 -> MAY)
I have tried to put every record that is not like '23 MAY 1946' in a temporary table, deleted all the records where len(date_texte) = 4 so I can eliminate yyyy format and tried to update every particular format at a time.
I have started with the format dd/mm/yyyy and used this:
set date = CONVERT(VARCHAR(2), DATENAME(DD, date_texte), 100) + ' ' + CONVERT(VARCHAR(3), DATENAME(MM, date_texte), 100) + ' ' +
CONVERT(VARCHAR(4), DATENAME(YYYY, date_texte), 100)
where date like '%/%' and ISDATE(date) <>0
This works for one of the servers I am working on and does not work for another server and I have to update in both places. Does anybody know what problem can this update arise on different servers?
And also, is there a better way to update all different date formats saved as varchar into a certain date format?
Thank you!
June 22, 2011 at 7:52 am
Wow this a classic example of why using the datetime datatype is so important. You have created a bowl of pasta and you are struggling with how to stir the broken ends of the spaghetti strands to put it back in the same bowl. The best way to handle this is to change your datatype to a datetime and use the front end for presentation.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 22, 2011 at 8:01 am
Yes..I know..:)
The problem is I haven't created it, I just have to fix it..:)
Anyway, thank you for your reply!
June 22, 2011 at 8:11 am
Sorry if my first post sounded nasty. Apparently I was channeling a certain individual who posts here frequently.
If you are going to be converting this into a datetime field my suggestion would be to create a new datetime column. Then you can update that field with values from the "pasta bowl". Once you have eaten all the pasta (or at least as much as you can), you will have your data in a nice and tidy datetime column with an easy way to identify which ones still need work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 23, 2011 at 1:54 am
Thank you for your answer!
I am not allowed to change the column to datetime so that the users will not be restricted to a datetime column. The users will add a certain text for an event and the text reffers to a certain period or specific time.
I will try to update the ones that can be identified through ISDATE() function or use some REGEX functions.
My next problem with this issue is the fact that I have two servers to work with and the default language for one server is English and for the other is French. Is there a way to get the name of the month (maybe DATENAME) in english from the server in French?
Ex. SUBSTRING(DATENAME(MM,@date),1,3) -> JUI (for the server in French) and I want to get JUN.
I need to find all the events that occured today (getdate()) by using the varchar column of date and my query works for the server with the default language English, but does not work for the server with the default language French. In the varchar column part of the dates are saved with the name of the month in English (23 JUN 2011) and those are the ones that need to be treated.
June 23, 2011 at 3:03 am
June 23, 2011 at 7:30 am
Be careful with the IsDate function. It will return values that can be parsed as a date that are probably not actually realistic for your application. For example:
select IsDate(5421)
There are plenty of other things that will evaluate to a valid date using IsDate that are not really accurate. The IsDate function is pretty forgiving about what it is passed. It is certainly a good start at filtering out values but by itself it is likely not sufficient.
Too bad you can't change the datatype of the column. I would argue the case up and down that it needs to be changed. Need an example of why it is bad to use incorrect datatypes as an example? Point your boss no further than the mess somebody else left you to clean up right here. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 24, 2011 at 1:28 am
Thank you for your answers.
I will keep the varchar column as it was and add a new table that will be populated with the types of dates.
Next I will add to my table three columns, date1, date2 and id_type_date.
Date1 and date2 will be datetime columns and I will filter all the dates from the varchar column into those two new columns.
Date1 will contain all the dates in the format yyyy; mmm yyyy; dd mmm yyyy; dd/mm/yyy; dd.mm.yyy; dd-mm-yyyy
And next for the format 'from.. to ..' or 'bet..and..' I will add the first date in date1 and the next one in date2 and identify the type through id_type_date.
For this time this will probably be the best solution.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply