1

I have an ASP.NET program that is writing date to an SQLExpress Database date field in DD/MM/YYYY.

When I look at the data in SQL Express it is stored as mm/dd/yyyy.

How can I configure it to store in DD/MM/YYYY format?

TeaDrinkingGeek
  • 1,995
  • 5
  • 34
  • 52
  • For instance I'm writing to the datbase a date 11/04/2011 09:48:33. When I look in the database date field, its 04/11/2011 09:48:33 – TeaDrinkingGeek Apr 11 '11 at 08:49
  • I have had to deal with lots of issues with Date locale so I suggest you that you test with dates where you cannot switch month and date and still have a valid date. So, if you try with 15/04/2011, you will know if it is reading it in the format you expect or not (it will fail because there is no 15th month). That will give you more info. – SJuan76 Apr 11 '11 at 09:05
  • It fails, so strangely, the date in the database is correct dd/mm/yyyy. Its only when I insert a records it swaps the dd and mm around and stores it. Weird huh. – TeaDrinkingGeek Apr 11 '11 at 10:06
  • When using dates in SQL Server, I would recommend to always use the ISO-8601 format (`YYYYMMDD`), which is language- and locale-independant and works always – marc_s Apr 11 '11 at 10:19

4 Answers4

4

This is not possible, as the date is internally stored as a number, the DD/MM/YYYY or MM/DD/YYYY format is only the display format of the data. You can, however, change the way the data are converted to a string by SQL functions...

Tobias Schittkowski
  • 2,221
  • 2
  • 16
  • 25
1

You are seeing the a rendered, localised version of an internal date representation (numbers of days since 01 Jan 1900 basically).

Don't worry about it. You'll get date back to your client (in an internal date representation) and this can be formatted how you like there.

gbn
  • 422,506
  • 82
  • 585
  • 676
  • How about if I try and read a record from the database using dd/mm/yyyy. Would I have to convert it to mm/dd/yyyy. Surely that is ridulous. – TeaDrinkingGeek Apr 11 '11 at 08:56
  • 1
    @TeaDrinkingGeek: you **aren't** reading in either dd/mm/yyyy or mm/dd/yyyy. The tool you are using to view the data is rendering it this way using either your regional setting or the database setting. – gbn Apr 11 '11 at 09:01
  • Problem is if I read the string back in using this: Convert.ToDateTime(MyReader["Date"]).ToString("dd/MM/yyyy"); It reads it has mm/dd/yyyy. – TeaDrinkingGeek Apr 11 '11 at 09:53
  • @TeaDrinkingGeek: is it stored as date in the database? – gbn Apr 11 '11 at 09:56
  • I checked the database its storing as dd/mm/yyyy. So why does it swap the dd and mm around? If I do this: Insert NoteBook (Date, Note) Values ('11/04/2011 11:02:46', 'test') it thinks the 04 is dd and 11 mm. – TeaDrinkingGeek Apr 11 '11 at 10:00
  • Read answers here http://stackoverflow.com/questions/1138142/best-way-to-convert-and-validate-a-date-string . '11/04/2011 11:02:46' is 04 Nov because of SQL Server language settings. This is different to your question which is about select. Read, then ask what you really want to know. – gbn Apr 11 '11 at 10:31
0

Maybe you can select that like this:

select CONVERT(varchar(12) , getdate(), 103 )
Conner
  • 30,144
  • 8
  • 52
  • 73
Lake
  • 46
  • 1
0

Store the data normally. When you retrieve the data, do something like this on the code:

dateField.ToString("dd/MM/yyyy")

And your result will be: 11/04/2011

Davidson Sousa
  • 1,353
  • 1
  • 14
  • 34