Date Format Problem; need 'dd/mm/yy' not 'mm/dd/yy' RRS feed

  • Question

  • User1641078693 posted
    I have the following problem when supplying a date to a sp date parameter: If I run with parameter formatted as 'dd/mm/yy' then I get 0 records because, I guess, sql expects first part to be 'mm'. I have windows and EM set as 'English' (i.e. 'dd/mm/yy'). The values in the tables also display as 'dd/mm/yy', so: 1) WHY must I supply a date parameter for a sp as 'mm/dd/yy'. 2) How should end users enter the date. 3) Can I store 'dd/mm/yy' and 'mm/dd/yy' format dates in the same table? Please help, I am a little confused. Thank you in advance. Regards - Rehman
    Wednesday, April 28, 2004 8:47 PM

All replies

  • User-528039901 posted

    ::WHY must I supply a date parameter for a sp as 'mm/dd/yy'. Because of the serers on the settings. BTW - noone says you need to provide this format, you can ALSO provide a time agnostic form, known as ISO form (well at least on sensible database systems - you interesting enough forgot to tell us what datbase you use). RTFM and look up on international date and time formats. Something along this: yyyy-mm-dd should also be accepted. This is the ISO norm. ::How should end users enter the date. In THEIR SET LOCALE. If I come to you with a browser saying I am german, with german locale, it is YOUR task to deliver dates to me in german formatting, and parse german formatting. Which is extremely easy given that you simply have to do nothing to do exactly this. Just parse it. ::Can I store 'dd/mm/yy' and 'mm/dd/yy' format dates in the same table? You do not and you should not. See, any sensible program will store data not as string but as a date & time value. DateTime struct in .NET, datetime / smaldatetime field type in sql server. And these do not store your formatting, they store the VALUE in it. What you see is then only the presentation. So, if you do not marvelously **** up by storing the date as a string (which was, btw., very common in anvient times in cobol) you will store it in a presentation independant format ANYWAY. Which means that you simply do not care about this. And it means you can not store the two forms in the table, as NO form is stored, just some numbers that represent a point in time. Hoiw do you deal with this? SImply by moving datetime formats (structures) back and forth. And using international form when writing / generating SQL Strings.

    Thursday, April 29, 2004 12:23 AM
  • User-522379131 posted

    IRI CoSort and likely NextForm (data migration tool) has date masking for re-structuring the date elements into the target column format. See http://www.iri.com/blog/data-transformation2/date-format-masking/

    Tuesday, April 1, 2014 12:05 PM