Question Converting nvarchar value to date and exporting to Excel date formatting issues

[thebatfink]

Hi, I wasn't entirely sure where to post this as I don't know if it's a problem with SQLCE, my SQL syntax or Excel. But here is my issue.
I have dates input into a field in my SQLCE field. The field is format DATETIME. I load up a datatable with output from my SQL query and then cycle through all the fields in the datatable and copy their values to an open spreadsheet.
I think the problem is caused by be outputting a string maybe TempDataTable.Rows(iX)(iY).ToString() ?

SQLCeDA = New SqlCeDataAdapter("SELECT RawDataTable.PlanDay, PolyTable.PolyType, SUM(RawDataTable.Length) [Length (LM)] " & _
"FROM PolyTable " & _
"INNER JOIN RawDataTable on PolyTable.RawDataID = RawDataTable.RawDataID " & _
"GROUP BY PolyType, PlanDay ORDER BY PlanDay", SQLCon)

If SQLCon.State = ConnectionState.Closed Then SQLCon.Open()
 
TempDataTable = New DataTable
SQLCeDA.Fill(TempDataTable)
For iC = 0 To TempDataTable.Columns.Count - 1
 ExcelWorkSheet.Cells(1, iC + 1).Value = TempDataTable.Columns(iC).ColumnName
 ExcelWorkSheet.Cells(1, iC + 1).font.bold = True
Next
ExcelWorkSheet.Rows(2).select()
For iX = 0 To TempDataTable.Rows.Count - 1
 For iY = 0 To TempDataTable.Columns.Count - 1
  ExcelWorkSheet.Cells(iX + 2, iY + 1).value = TempDataTable.Rows(iX)(iY).ToString()
 Next
Next

When I look on the worksheet, it is showing strings for dates which do not adhere to the MM/DD/YY format, ie if my date is 13th or higher, it shows a string in the cell (because there isn't a 13th Month presumably) for example 25/01/2012. But where the date is 12th or below, it is converting the string to a date using MM/DD/YYYY for example, my date 01/02/2013 is being converted to 2nd January 2013.
I have tried modifying the SQL to this..

SQLCeDA = New SqlCeDataAdapter("SELECT CONVERT(nvarchar(10), RawDataTable.PlanDay, 103) as [Date], PolyTable.PolyType, " & _
"SUM(RawDataTable.Length) as [Length (LM)] " & _
"FROM PolyTable " & _
"INNER JOIN RawDataTable on PolyTable.RawDataID = RawDataTable.RawDataID " & _
"GROUP BY PolyType, PlanDay ORDER BY PlanDay", SQLCon)

I believe this takes the datetime value and changes it to a string with the formatting I want ie DD/MM/YY, but still when this is pasted into Excel, its formatting dates which could be considered in the format MM/DD/YY.
If any of that makes sense, I would really appreciate any help sorting out the problem
Thanks!

 

[thebatfink]

Don't suppose anyone has any ideas regarding this? I'm struggling to fix the problem. Thanks.

 

[jmcilhinney]

Check whether the field value is type DateTime and, if it is, cast as that type and pass a format string when you call ToString.

 

[thebatfink]

Check whether the field value is type DateTime and, if it is, cast as that type and pass a format string when you call ToString.

Hi, Field Type is definitely DateTime but it has the time portion in the field also. I want to extract only the date portion from the field and insert that as the Excel sheets cell value.

I'm not sure what you mean to cast as that type and format the string? If I just try to fire the raw data in the date field into the spreadsheet (without the convert in the sql statement to trim off the time) it still gets upset with the date format.

Here is an example of the values in the DB -

02/02/2013 06:00
04/02/2013 06:00
11/12/2013 06:00
25/02/2013 06:00
27/02/2013 06:00

but in the worksheet these dates are showing as

02/02/2013 06:00 - these have the dd and mm transposed
02/04/2013 06:00 - these have the dd and mm transposed
12/11/2013 06:00 - these have the dd and mm transposed
25/02/2013 06:00 - these stay correctly formatted
27/02/2013 06:00 - these stay correctly formatted

If I look at what cell type has been given to these cells in the spreadsheet, I find the transposed dates are of Custom Format dd/mm/yyyy hh:mm and the normal dates have a general format.

I assume tostring is just converting these dates to strings, maintaining the correct formatting, but for some reason excel is deciding its either not a date and accepting the string as provided (but I want excel to treat as a date), or it thinks it is a date formatted in mm/dd/yyyy and converts it to dd/mm/yyyy when infact I gave it a dd/mm/yyyy in the first place.

EDIT:

I have even tried setting the numberformat before passing the cell value to force the cell to type dd/mm/yyyy and whilst the cell does show as formatted this way, those dates are still having the dd and mm transposed

ExcelWorkSheet.Cells(iX + 2, iY + 1).NumberFormat = "dd/mm/yyyy"
ExcelWorkSheet.Cells(iX + 2, iY + 1).value = TempDataTable.Rows(iX)(iY).ToString()

 

[thebatfink]

Argh I finally solved this. Had a mess around again today and tried out

SQLCeDA = New SqlCeDataAdapter("SELECT CONVERT(nvarchar(10), RawDataTable.PlanDay, 101) as [Date], PolyTable.PolyType, " & _
"SUM(RawDataTable.Length) as [Length (LM)] " & _
"FROM PolyTable " & _
"INNER JOIN RawDataTable on PolyTable.RawDataID = RawDataTable.RawDataID " & _
"GROUP BY PolyType, PlanDay ORDER BY PlanDay", SQLCon)

(changing the 103 date format to 101) and it works OK.. MSDN site says 103 was the correct format for UK dates, but it seems Excel gets confused when passed this type? Passing dates converting to 101, Excel accepts them ok and correctly formats the cell types also :/ that was hard work!

 

[jmcilhinney]

Or you could have just done what I said:

Dim data As Object = TempDataTable.Rows(iX)(iY)

ExcelWorkSheet.Cells(iX + 2, iY + 1).value = If(TypeOf data Is Date, CDate(data).ToString("MM/dd/yyyy"), data.ToString())