2

I'm using SSMS 2012 and I'm simply trying to convert a datetime2(7) field to a date field - however, no matter how I try, the spreadsheet (Excel 2016) I have linked to it returns the field as text. I'm at my wit's end! I've tried:

SELECT CONVERT(datetime2(7), dbo.SchoolDays.DayDate, 103) AS DayDate

SELECT CAST(dbo.SchoolDays.DayDate AS date) AS DayDate

SELECT CAST(CONVERT(datetime2(7), dbo.SchoolDays.DayDate, 103) AS date) AS DayDate

And some other functions - too many to list here as I've been at it for two hours.

I really need it to come across into the linked spreadsheet as a date, as I want the entries returned to be limited to a date range entered on the spreadsheet in a particular cell.

Any suggestions please?

Grizzle
  • 21
  • 1
  • 2
    You'll find the answer here: http://stackoverflow.com/questions/39459706/sql-server-datetime-object-persistent-reformatting-issue-in-excel/39469650#39469650 Basically, Excel does not understand SQL's `date`, or `time`, or `datetime2`. Excel understands only `datetime`. So, if you `cast` or `convert` to `datetime` then it is recognized as such. All other formats are not. – Ralph Sep 23 '16 at 10:44

0 Answers0