1

I am converting a Google Sheet to a SQL database table. When it loads the Excel sheet (I copy the Google Sheet data to Excel and import it) SQL changes some of the data types. I have a column in my Excel sheet that is a date that SQL converts to nvarchar. When I try to convert it back to a date data type, I get this error:

"Conversion failed when converting date and/or time from character string."

My code seems to be correct...I'll show you.

USE Sample
ALTER TABLE ['RM Item List$']
ALTER COLUMN [Kosher Cert] date

Can someone give me an idea why this doesn't work? I know questions about data conversion have been asked ad nausea, but I didn't see an answer that helped me solve my problem. SQL converted my dates in the Kosher Cert column to strings of five numbers. Any help would be appreciated.

bmsqldev
  • 2,627
  • 10
  • 31
  • 65
Ethan
  • 11
  • 1
  • Please provide a sample of the data stored in the `[Kosher Cert]` field, you say they are numbers, along with the date that you think it should be. – mendosi Nov 14 '16 at 00:19
  • Ok, an example. The Kosher Cert field is a date for kosher certification for foods at my work. SQL converted 1/31/2017 to 47266. It changed the date format to nvarchar. Another example...SQL changed 12/31/2016 to 42735 in another row. – Ethan Nov 14 '16 at 00:42
  • Excel stores dates internally as numbers, http://excel.officetuts.net/en/training/how-excel-stores-date-and-time, it looks like it is Excel's internal number that are getting written to your table (assume that 31 Jan 2017 is actually 42766 not 47266 as you typed). Since this seems to be the case, then I think my answer should be your best way forward. – mendosi Nov 14 '16 at 00:47
  • You're right mendosi. I mistyped the first date. It was 42766 in SQL. Thanks. – Ethan Nov 14 '16 at 01:02

1 Answers1

0

This related topic might be helpful: T-SQL to convert Excel Date Serial Number to Regular Date

It would probably make most sense to add a column to your table with the date type, populate it and then drop the nvarchar column:

ALTER TABLE ['RM Item List$'] ADD [KosherCert] date;
UPDATE ['RM Item List$'] 
   SET [KosherCert] = DateAdd(Day, Convert(int, [Kosher Cert]), '1899-12-30');
Community
  • 1
  • 1
mendosi
  • 2,001
  • 1
  • 12
  • 18