0

I am inserting some data into a table on different SQL server , for this purpose i have created a dtsx package , We need data in destination table in dd-mm-yyyy format and i am using below query to convert date into desired format 

Convert(varchar,dbo.Member.DateOfBirth,105)

when run query in SSMS , it gives perfectly converted data as '25-08-1989' but when i check it in destination table after insertion, it show date as '1989-08-25'
Can somebody tell me why destination table is not accepting date in dd-mm-yyyy format after conversion ?
I am new here , sorry for formatting mistakes ....

  • 1
    You don't store dates in certain formats in the database (and please, no varchar fields). You store them in a proper data type (date, datetime2 etc) and do formatting in the frontend. – James Z May 05 '17 at 06:33
  • @means formatting wont work when storing , have to convert when about to use it ??? –  May 05 '17 at 06:34
  • Yes, the data is stored in a binary format and it doesn't contain any formatting. You need to do that elsewhere – James Z May 05 '17 at 06:37
  • @JamesZ ok , one thing is not clear "(and please, no varchar fields)" , why ? –  May 05 '17 at 06:40
  • 2
    [Sql server does not store date display format.](http://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028) If you are using the proper data type (date, datetime2, datetime or time) you don't need to worry about string format at all. To represent a date value as strng always use `yyyy-mm-dd` (and with time `yyyy-mm-ddThh:mm:ss.nnn`) to avoid ambiguity – Zohar Peled May 05 '17 at 06:42
  • @ZoharPeled - `yyyymmdd` please, if no time component. On older versions, SQL Server could interpret the dashed form as `yyyy-dd-mm`. E.g. connect to a 2005 server and execute `set language british; select DATEPART(month,'2017-05-09')` and you'll get `9`, not `5`. – Damien_The_Unbeliever May 05 '17 at 07:07
  • @Saurabh there is no such thing as a date format. They are binaries. You *don't* need any specific format in your target table. Either you have a bug in your schema and used varchar instead of `date`, or you perrforming a useless conversion. If `DateOfBirth` is a date type, just store it directly to the target date-typed column – Panagiotis Kanavos May 05 '17 at 07:08
  • @Damien_The_Unbeliever I wasn't aware of that, thanks!. btw, how old? – Zohar Peled May 05 '17 at 07:09
  • @Saurabh post the target table's and `Member'`s schema. There's no need to convert either in SQL Server or SSIS, so you need to find where that varchar came from and fix the incorrect type – Panagiotis Kanavos May 05 '17 at 07:09
  • 1
    @ZoharPeled the rule is that only the unseparated format is unambiguous for dates, ISO8601 for datetimes. – Panagiotis Kanavos May 05 '17 at 07:11
  • @Saurabh are you confusing your *client's* display format for some server format? SSMS is nothing more than a client. In order to display the data *it* formats the date using a specific string. *Your* application or report will use a different one, typically the one that corresponds to your locale. – Panagiotis Kanavos May 05 '17 at 07:13
  • My comment about varchar fields was related to all the questions in SO where people have big problems with dates because someone earlier decided to store a date into a varchar, maybe because they wanted a certain format -- and that's going to be a lot of pain later when you actually need some date logic – James Z May 05 '17 at 09:46

1 Answers1

0

You are probably confusing the client's way of formatting dates for some (non-existent) server date format. There is none. All date types are binary values, just like int, float and decimal. The client formats them into strings in order to display them.

SSMS is nothing more than a specialized client. By default, dates are formatted according to your (the user's) locale. This can lead to confusion when the same date appears as 25/9/2017 to one user but 9/25/2017 to another. The date though is still the same.

DBAs and developers don't care about localized formats though. SSMS uses YYYY-MM-DD to avoid confusion.

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236