How to change multiple date formats in Sql server
-
Hi I have legacy database that contain millions of records it has a column (DATA TYPE IS nvarchar) in database that has values in four date formats (5/29/2014) and (2014-05-15) and (16/4/2014) and (Apr 7 2014 4:42PM) I want to convert it into (16/04/2014). When i use convert function it gives error message Msg 245, Level 16, State 1, Line 2 Conversion failed when converting the nvarchar value 'Apr 7 2014 4:42PM' to data type int. We have so many scattered format so we cannot run a single query for specific data. so kindly provide solution for this problem. these formats i have been using this statement to convet data. select CONVERT(varchar(10), CONVERT(date,FormReceivedDate, 101), 103) from Applicant where FormReceivedDate=1
-
Hi I have legacy database that contain millions of records it has a column (DATA TYPE IS nvarchar) in database that has values in four date formats (5/29/2014) and (2014-05-15) and (16/4/2014) and (Apr 7 2014 4:42PM) I want to convert it into (16/04/2014). When i use convert function it gives error message Msg 245, Level 16, State 1, Line 2 Conversion failed when converting the nvarchar value 'Apr 7 2014 4:42PM' to data type int. We have so many scattered format so we cannot run a single query for specific data. so kindly provide solution for this problem. these formats i have been using this statement to convet data. select CONVERT(varchar(10), CONVERT(date,FormReceivedDate, 101), 103) from Applicant where FormReceivedDate=1
I would suggest using the DATE data type. SQL server saves the date as 20140416. You only need to convert(date, FormReceivedDate). Formatting of the data is done for output.
-
Hi I have legacy database that contain millions of records it has a column (DATA TYPE IS nvarchar) in database that has values in four date formats (5/29/2014) and (2014-05-15) and (16/4/2014) and (Apr 7 2014 4:42PM) I want to convert it into (16/04/2014). When i use convert function it gives error message Msg 245, Level 16, State 1, Line 2 Conversion failed when converting the nvarchar value 'Apr 7 2014 4:42PM' to data type int. We have so many scattered format so we cannot run a single query for specific data. so kindly provide solution for this problem. these formats i have been using this statement to convet data. select CONVERT(varchar(10), CONVERT(date,FormReceivedDate, 101), 103) from Applicant where FormReceivedDate=1
AzeeM_R wrote:
Conversion failed when converting the nvarchar value 'Apr 7 2014 4:42PM' to data type int
You shouldn't be trying to convert the data to an
int. SQL has specific data types for storing date/time data[^], which you should be using instead. Theyyyy-MM-ddandMMM d yyyy h:mmTTformats are easy to convert:SELECT
Convert(date, '2014-05-01'),
Convert(datetime2(0), 'Apr 7 2014 4:42PM')
;The other two formats will be impossible to convert unless your data contains another field indicating whether they're
dd/MM/yyyyorMM/dd/yyyy. Without that additional field, there would be no way to know whether01/02/2014should be 1st February or 2nd January. If you do have an additional field indicating the format of the date, then your options depend on the version of SQL that you're using. SQL 2012 and 2014 have thePARSEmethod[^]:SELECT
PARSE('5/29/2014' As date),
PARSE('16/4/2014' As date USING 'en-GB')
;For older versions, you would need to write your own function to parse the string.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Hi I have legacy database that contain millions of records it has a column (DATA TYPE IS nvarchar) in database that has values in four date formats (5/29/2014) and (2014-05-15) and (16/4/2014) and (Apr 7 2014 4:42PM) I want to convert it into (16/04/2014). When i use convert function it gives error message Msg 245, Level 16, State 1, Line 2 Conversion failed when converting the nvarchar value 'Apr 7 2014 4:42PM' to data type int. We have so many scattered format so we cannot run a single query for specific data. so kindly provide solution for this problem. these formats i have been using this statement to convet data. select CONVERT(varchar(10), CONVERT(date,FormReceivedDate, 101), 103) from Applicant where FormReceivedDate=1
If you convert the garbage you have to a single string format and store it as varchar you are perpetuating the same garbage as you are having to deal with now. USE THE DATETIME DATA FORMAT. And yes I did mean to shout. You might need to do a number of passes through the data using the suggestion from the other replies to convert the data. Records that are ambiguous may require the mark I eyeball to get the context of the date from other data in the record. Altogether a thoroughly nasty job 100% caused by not storing the data in the correct format. I know it is a legacy app but if you can add new fields in the correct format then your life will be possible and the users cannot repeat the input of garbage.
Never underestimate the power of human stupidity RAH
-
If you convert the garbage you have to a single string format and store it as varchar you are perpetuating the same garbage as you are having to deal with now. USE THE DATETIME DATA FORMAT. And yes I did mean to shout. You might need to do a number of passes through the data using the suggestion from the other replies to convert the data. Records that are ambiguous may require the mark I eyeball to get the context of the date from other data in the record. Altogether a thoroughly nasty job 100% caused by not storing the data in the correct format. I know it is a legacy app but if you can add new fields in the correct format then your life will be possible and the users cannot repeat the input of garbage.
Never underestimate the power of human stupidity RAH
