Date Formatting Issue

  • Hi,

    I'm running SQL Server 2005. The installation is set to use the dd/mm/yyyy (British date format). I have a database that appears to be running under the same format. I've checked the data in the tables and all appears to be the same. However, any stored procedures I write for this database, the date format of any input parameters needs to be in mm/dd/yyyy (US date format)

    Does anyone know how I can fix this issue? I've tried adding SET DATE FORMAT dmy in the stored procedure, but this doesn't work.

  • you can change your us date to brit format by

    convert(char(10), , 103)

    or convert to us date using

    convert(char(10), , 101)

  • Thanks for your response. However, should I really be having to convert a date back to the format I want. I just can't understand why this database is running like this. Very confusing. All other databases on this instance work fine apart from this one

  • The server AND each database have their own collation settings. Could it be that that DB is set incorrectly?

    Take a look at this over thread: http://www.sqlservercentral.com/Forums/Topic305998-266-1.aspx

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • How do you know the stored procedures are expecting US Format? What happens if you run a query against the table using a date field in where clause? How are you creating the SP's (Query Analyzer in SSMS, Visual Studio, Some other tool)?

  • I was doing this for example

    DATEADD(m,5,@pdteStartDate)

    and 5 months was being added to the day component rather than the month component of @pdteStartDate, however when I passed in the same parameter but in US format it worked fine.

    The database wasn't created by me but is the backend of an off-the-shelf helpdesk call management system. The collation is set to SQL_Latin1_General_CP1_CI_AS wheras the server default is Latin1_General_CI_AS. Would this make a difference?

  • Got it to work doing this as recommended by Joel

    SET @pdteStartDate = CONVERT(char(10), @pdteStartDate, 103)

  • Is the parameter/variable defined as datetime or smalldatetime?

    AS far as the collations they are the same. Both are US English Case Insensitive and Accent Insensitive and I do not believe they affect dates.

    The date comes from the language setting. You can issue the Set Language to change the language for a session.

  • I've tried both datetime and smalldatetime and have the same issue with both

  • Seems like the confusion is about how to enter the date into the variable?

    And also maybe combined with confusion on how a datetime is presented when retrieved from a variable or a column.

    The important part is to ensure that a date is parsed and written to the db as intended.

    When we use for example '/', this becomes very risky business.

    My recommended format is yyyymmdd (with no delimiters)

    It's always safe.

    Here's a good article on the subject.

    http://classicasp.aspfaq.com/date-time-routines-manipulation/how-do-i-delimit/format-dates-for-database-entry.html

    /Kenneth

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply