Click here to Skip to main content
15,868,040 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have an excel from where I am bulk importing data to SQL server through .Net code (Dataset.WriteToServer method).
Now, my excel has dates in m/d/yyyy format, e.g. 9/21/2021. The destination SQL Server column is varchar and I would prefer not to have to change it in this release.
When we run this in one app server, its importing as '9/21/2019' in SQL Server, which I want.
However, in another app server, its being imported as '21/9/2021' for the same excel file. This is causing some functionalities to fail in the second server

What I have tried:

We checked the System date and time formats in the two servers and in the second server (where the problem occurs), it was d/m/yyyy. We are changing that to m/d/yyyy. Now I have 2 questions,
1. Any other changes required at Windows or SQL Server level to do this without code change?
2. Do we need to reboot the system/restart IIS or App pool for this change to take effect?
Thanks in advance
Posted
Updated 30-Mar-22 3:21am
Comments
Richard Deeming 30-Mar-22 9:18am    
Don't store dates as strings! If you used the correct data type to store your dates, then all of these problems would go away.

If you insist on using the wrong data type, then you're going to have to format the DateTime you retrieve from your Excel sheet before you pass it to SQL:
Custom date and time format strings | Microsoft Docs[^]

1 solution

Quote:
The destination SQL Server column is varchar and I would prefer not to have to change it in this release.


That's a big mistake. It's simple to get the data from excel into the field, but once it's there, it's there until you need to use it - and at that point you will start to get problems. NVARCHAR fields should only be used for text data - mobile numbers, names, addresses, product descriptions: anything you aren't going to try to compare numerically or sort by anything other than a basic string comparison.
And that includes dates. In a string based sort, the comparison is wholly based on the first pair of different characters in the two strings, everything else is ignored.
so "2/3/2022" is before "4/3/1955" but after "1/3/2100".

And converting this when you need to use it is a nightmare as someone. somewhere, somewhen will put "today" or "I dunno" in there instead and your code will fail.

Bite the bullet, change your datatype to DATE, DATETIME, or DATETIME2 and the "month first" / "day first" problem becomes just a matter of preference in the presentation software, instead of a nightmare that will haunt you for years to come.
 
Share this answer
 
Comments
Bedabrata Bhattacharya (IN) 31-Mar-22 0:50am    
Thanks for the answer. I hear you and agree that this is just poor design. Using proper data format will be the only reasonable solution in the long term. Problem is, I just came in to the project and the coming release is this weekend. Making any changes in code now is extremely risky as this is a complex application and I don't know where else there might be dependencies. I have already planned to get this corrected as soon as we are done with this release. Unfortunately, I do not have the time/resources to get this done and tested before this release. Hence, need to find some temporary solution for a few days. Anyway, thanks for the insights.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900