0

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

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
Bedabrata Bhattacharya IN
  • 1
  • 2
    "...in one app server..." - I assume this means you are importing the Excel file in your app tier. What is the real mechanism that you are using to read the Excel file and send it to SQL Server? – squillman Mar 30 '22 at 12:15
  • 2
    `The destination SQL Server column is varchar and I would prefer not to have to change it in this release` Why do you prefer not to fix this error in the design ? It will keep giving you troubles. Store a date in a Date column, not in a varchar. – GuidoG Mar 30 '22 at 12:20
  • As for your 2 questions, the date format of your servers is irrelevant, sql server does not have a format for dates (which also would not matter because you want to store it wrong as varchar). If you really want to store the date as varchar then you need to change the code that reads the excel and do the correct formatting there – GuidoG Mar 30 '22 at 12:25
  • 1
    Fix the column. There is no possible advantage to storing dates in a varchar column except you avoid having to write an `ALTER TABLE` command (and dealing with any garbage data you've already stored). – Aaron Bertrand Mar 30 '22 at 12:30
  • 1
    Also, you'll still have to deal with whatever problems these terrible formats cause. What date is `5/6/2022`? Sounds like in some places in your system that will be interpreted as May 6th, and in others, June 5th. This is why we avoid regional and ambiguous formats as much as possible. `20220506` is perhaps a little less pretty but it cannot be misinterpreted by SQL Server or, hopefully, any human you have to work with. See [Dating Responsibly](https://sqlblog.org/dates). – Aaron Bertrand Mar 30 '22 at 12:36
  • welcome to stackoverflow bedabrata. i wish to check something. **I am bulk importing data to SQL server through .Net code (Dataset.WriteToServer method)** - does the import on both SQL instances using this .net code? – Bagus Tesa Mar 30 '22 at 13:02

1 Answers1

0

Thanks to all for your responses. The immediate issue was resolved after changing the system date setting in App servers. However, to avoid managing the nightmare of junk data going into prod (and the other issues all of you correctly highlighted), I did bite the bullet and changed the DB column data type to varchar and let excel and sql talk to themselves with two dates. Issue is of course resolved and I noticed one additional benefit with the second approach which I would like to share.Now the validation whether user is uploading a correct date is being done on the client side itself and the business logic processing in DB is much faster.

Bedabrata Bhattacharya IN
  • 1