0

I have two columns in SQL Server. I have a SQL bulk insert and it stores date in following format inside the database:

  1. The first column type is DATETIME - it stores dates in this format: 2018-07-02 00:00:00.000

  2. The second column type is DATE and it stores this format: 2018-07-02

The below part is with out using bulk insert.

When I insert new date its format is changing. I have following code in my C# for first column and second column

DateTime.ParseExact(txtODR.Text, "dd/MM/yyyy", null);
DateTime.ParseExact(txtWorkCompDt.Text, "dd/MM/yyyy", null);

When I look in SQL Server, it stores in this way "2018-02-07 00:00:00.000" and "2018-02-07"

Why it doesn't store in required format when I try to insert/update from C# (2018-07-02 00:00:00.000 & 2018-07-02). What is wrong here? Please help me

SQL Code

 DECLARE @P_ORDER_RECIVED_DATE NVARCHAR(100) = '2018/15/02'
 UPDATE [dbo].[T_INSTALATION]
       SET [Order_Recieved_Date] = CONVERT(VARCHAR,@P_ORDER_RECIVED_DATE, 123)   
       where Order_Install_ID = '161'
atc
  • 621
  • 8
  • 31
  • 7
    [Dates are not stored with display format in SQL Server](https://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028). The dates in your sample data may be interpeted as `July 2nd 2018` or as `February 7th 2018`. try to check with a date like `15/07/2018`. – Zohar Peled Feb 25 '18 at 11:09
  • `What is wrong here` Your failure to show the code where you insert/update from C#. – mjwills Feb 25 '18 at 11:29
  • What is the data type of `Order_Recieved_Date` column? – Shiblu Feb 25 '18 at 12:11
  • datetime is the type – atc Feb 25 '18 at 12:16
  • There is no Year-DAY-Month format support in SQL Server as far as I could tell. To parse 2018/15/02 you have to manually extract the day month etc. portion – Shiblu Feb 25 '18 at 12:26
  • Why are you passing strings around? The .Net DateTime maps directly to SQL Server's DateTime. Just send an instance of DateTime as a parameter to SQL Server and you won't need to worry about string representation formats ever again. – Zohar Peled Feb 25 '18 at 12:28

2 Answers2

1

This depends on the Environment setting of your SQL Server, if your setting are being set as yyyy/MM/dd it will stay that way when you insert or update dates into the db as well, there is nothing wrong with your code.

There is a way of changing that manually using the FORMAT method of SQL Server, it will format the date regardless of the global config.

A sample T-SQL could be:

SELECT FORMAT(SYSDATETIME(), 'dd-MM-yyyy', 'en-US');

This will return 25-02-2018

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Barr J
  • 10,636
  • 1
  • 28
  • 46
0

dd/MM/yyyy can be easily confused with MM/dd/yyyy format.

For example, date 11/02/2018 might represent 11 February or November 2nd.

You are seeing what SSMS is displaying to you. To be on the safe side it is better to store the date in yyyy MM dd format. Regardless of storage format, you should use your own formatting in the SELECT statement.

Update

There is no support for yyyy-dd-MM format. You could manually extract the characters from the given string 2018/15/02 to create date. For future uses, I strongly suggest changing the date format to yyyy-MM-dd or dd/mm/yyyy in the project.

More info

Shiblu
  • 457
  • 3
  • 12