Date string to date

[tco9998]

Hello.

I want to ask how to convert date string into dates. I have a column with date string and i want a columns with dates.

I have Excel 2007.

Thanks.

[NBVC]

Give some examples of what you have and what your results would be.

[tco9998]

My problem is that i have an Excel column which stores values with a Date format+ of '01/01/2012' and I am trying to import the spreadsheet, into a SQL Server table with a
field that has a datetime datatype.

And this is not happening. And i am wondering if this is excel problem or not.

[tco9998]

please, any help?

[NBVC]

I am not understanding... your current spreadsheet has the dates formatted as dates, yes? I have never uploaded from Excel to SQL server... so not too sure about it...

[Pauleyb]

Use =DATEVALUE. That will return an integer for Excel's date, so you may need to change the format to date. Your problem is more likely on the SQL conversion side. I would think it would prefer the text input and it would convert to its internal datetime datatype.

[tco9998]

Dates DateValue
06/06/2012 41066
06/06/2012 41066
11/06/2012 41071
13/6/2012 #ΤΙΜΗ!
13/6/2012 #ΤΙΜΗ!
14/6/2012 #ΤΙΜΗ!
14/6/2012 #ΤΙΜΗ!
20/6/2012 #ΤΙΜΗ!
20/6/2012 #ΤΙΜΗ!
20/6/2012 #ΤΙΜΗ!
21/6/2012 #ΤΙΜΗ!
25/6/2012 #ΤΙΜΗ!
26/6/2012 #ΤΙΜΗ!
26/6/2012 #ΤΙΜΗ!
26/6/2012 #ΤΙΜΗ!
28/6/2012 #ΤΙΜΗ!
04/07/2012 41094
04/07/2012 41094
04/07/2012 41094


This is what i get with datevalue. I don't know any more. This seems not to be datetime but date strings.

[Pauleyb]

Like I said, DATEVALUE will return an integer. You will need to format it to a Date (short date), and it should match the text string. This is how Excel handles dates, just a formatted integer, which is why I doubted it would work for SQL.

I don't know what a #TIMH! error is. A Google search showed nothing, and the only error mentioned in the DATEVALUE help page is the #VALUE! error when the text string does not match a valid date. Is your system set up as dd/mm/yyyy? It seems to have given you errors on 'invalid' dates; however, 41094 is July 4th, 2012. Maybe those other dates are not text strings?

Regardless, I still think the SQL importer should be smart enough to convert date text strings to datetime format. I would recommend checking out some of the Google results for excel to sql transfers.