locked
Setting Date Format RRS feed

  • Question

  • Hi

    I have recently developed a web app on a win server 2003 server. Whilst the development was nearing completion, I purchased a new server running win server 2008 R2 and SQL Server 2008 R2 to host the app. Since moving the app and database to the new server, all dates within the app are now displaying in US format (mmddyyy) rather than UK format (ddmmyyyy) as they were previously. I checked the regional settings and it appears that the guy who installed and set up the server had left the default regional settings as English US. I changed all regional settings to UK but it doesn't seem to have made any difference concerning the app. Is there a regional setting within SQL Server that I need to change?

    Regards

    Steve Flynn

    Monday, January 24, 2011 9:21 AM

All replies

  • Hi Steve,

    Please look at the following link; - How to Set the Day/Month/Year Date Format in SQL Server. The following is an extract from this link; -

    Set the Language on the SQL Server

    To set the language on the server you must add a language by using sp_addlanguage. The example below sets the language for British English and gives the dates in DD/MM/YY format. The example can also be applied to other countries, but you may need to modify the parameters for sp_addlanguage.

    exec sp_addlanguage 'British', 'English',
     'January,February,March,April,May,June,July,August,September,October,
     November,December',
      'Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec',
     'Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday',
     dmy,1
     sp_configure 'default language', 1
     reconfigure with override
    

    Kind Regards,

    Kieran.


    If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/kieranpatrickwood
    Tuesday, January 25, 2011 10:49 AM
  • Hi Steve_Flynn,

     

    Please output date with Culture information. Or we could get every part of datetime and concatenate them manually to construct required format.

     

    Meanwhile since the date format is outputted by this application, this issue is less related to SQL Server. I would like to recommend that you ask it in Microsoft .NET Framework to get specific support.

     

    If anything is unclear, please let me know.


    Regards,
    Tom Li
    Wednesday, January 26, 2011 7:44 AM
  • Hi Keiran

    Thanks for your reply. Unfortunately I think this method might be referring to an older version of SQL Server. I am using 2008 R2 and although I can find the sp_configure procedure, the sp_addlanguage doesn't appear to be present. Is it possible that the same SP is named differently in 2008 R2?

    Tom Li

    As I stated in my original post, the application was displaying dates in the correct format whilst I was running it from a different server under 2008 Express. The problem only occurred when I moved the app and database to a new server with 2008 R2. Therefore I find it difficult to understand how this could be a .net related issue rather than SQL Server.

    Reagrds

    Steve Flynn

    Wednesday, January 26, 2011 10:07 AM
  • Sorry Steve,
    You are right it looks like sp_addlanguage has been deprecated. I am trying to find out why this is and what if anything has replaced it.


    If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/kieranpatrickwood
    Wednesday, January 26, 2011 5:17 PM
  • Hi Steve_Flynn,

     

    How did you output date in your application? Did you output it directly? Please paste the code here.

    We could control how they were displayed in the code. If it displays incorrectly while we use Culture, we also could consider to manually control the output format by get every part from this date and concatenate them to required format.

     

    If anything is unclear, please let me know.


    Regards,
    Tom Li
    Thursday, January 27, 2011 2:00 AM
  • Hi Tom Li

    The application outputs the date via stored procedures, e.g. (date field in bold)

    SELECT  product_id,
      item,
      CartonsPerPallet,
      QuantityPerCarton,
      Code,
      SupplierCode,
      CreatedBy,
      CreatedOn,
      DateSensitive,
      MinShelfLife,
      GoodsIn,
      UKHaulage,
      EIREHaulage,  
      Storage,
      RHAD,
      ReWork,
      PickPriority
    FROM  Products
    WHERE  product_id=@product_id

    I can work around this by converting the date in the SP as follows...

    CONVERT(varchar(50),CreatedOn,106)AS CreatedOn,

    Obviously, this isn't the most practical solution and I would rather have SQL handle the data in dd/mm/yy format from the start rather than writing extra code to manipulate the format.

    Regards

    Steve

    Thursday, January 27, 2011 12:24 PM
  • Hello,

    SQL Server 2008 introduces four new DATE and TIME data types namely TIME, DATE, DATETIME2 and DATETIMEOFFSET. Take a look at the following New Date and Time Data Types in SQL Server 2008 article to know more in detail.

    SQL Server 2008 introduces five new functions namely SYSDATETIME, SYSDATETIMEOFFSET, SYSUTCDATETIME, SWITCHOFFSET and TODATETIMEOFFSET. Take a look at the following Date and Time Functions in SQL Server 2008 article to know more in detail.

    Hope that Helps!

    Thanks
    Ashish Kumar Mehta

    Please click the Mark as Answer button if a post solves your problem!

     

    Thursday, January 27, 2011 3:23 PM
  • Hi Steve,

    Please also have a look at; -

    SET LANGUAGE (Transact-SQL)

    sys.syslanguages (Transact-SQL)

    Also what is your collation setting? (To find out right click on your database object name, then click on the options tab).

    Kind Regards,

    Kieran.


    If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/kieranpatrickwood
    Saturday, January 29, 2011 1:01 PM
  • Hi Kieran

    Collation setting is set as 'Latin1_General_CI_AS'

    Thanks

    Steve

    Monday, January 31, 2011 9:12 AM
  • Hi Steve,

    Thanks for your response. Your collation setting is the same as mine.

    I am continuing to chip away at this. I am confused still as to why the issue did not occur on SQL Server Express 2008 but did occur on SQL Server 2008 R2 on a different server.

    Sorry I'm not helping you as quickly as I would have wanted.

    Kind Regards,

    Kieran.


    If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/kieranpatrickwood
    Monday, January 31, 2011 12:58 PM
  • Hi Steve,

    Sorry again for the delay, please try; -

    SET LANGUAGE BRITISH
    SELECT CreatedOn FROM Products
    WHERE CreatedOn = '15/02/1999' -- or a specific date which exists within your table
    

    I hope this helps,

    Kind Regards,

    Kieran.


    If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/kieranpatrickwood
    Thursday, February 3, 2011 6:52 PM
  • Hi Keiran

    The query returns nothing when I change the date to a relevant value. I also tried adding the 'set language british' line to the start of one of my SPs but it didn't seem to make any difference.

    I have also been advised to try...

    ALTER LOGIN user1 WITH DEFAULT_LANGUAGE = British

    Didn't work

    Was sent the following article but it doesn't seem to apply to 2008 R2

    http://msdn.microsoft.com/en-us/library/ms190682.aspx

    The application has now gone live and this is causing serious HAVOC, at this rate I'll be turned grey by the end of the week!

    Steve 

    Friday, February 4, 2011 10:16 AM
  • Hi Steve,

    I'm surprised to hear your application has gone live with this issue. Just to clarify I tested the code I sent to you (on my previous post) on my SQL Server 2008 installation which had an initial american date set up and my code worked.

    Since you reported that your application worked under SQL Server 2008 Express but it doesn't work with SQL Server 2008 R2 I think your main focus should be on finding the differences between the two SQL Server systems.

    Sincere Regards,

    Kieran.

     


    If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/kieranpatrickwood
    Friday, February 4, 2011 1:17 PM
  • Kieran

    Where should I be running the code in your previous post? Do I need to run this as a one off query or include it with every SP in the database? Could you give an example?

    Thanks

    Steve

    Friday, February 4, 2011 4:13 PM
  • Hi Steve,

    I tested the following code simply by clicking on the "NEW QUERY" button whilst being connected to a database which initially had the date in american format; -

    SET LANGUAGE BRITISH
    SELECT CreatedOn FROM Products
    WHERE CreatedOn = '15/02/1999' -- or a specific date which exists within your table
    
    

    Since you reported that your application worked under SQL Server 2008 Express but it doesn't work with SQL Server 2008 R2 I think your main focus should be on finding the differences between the two SQL Server systems whether this be the system settings or the databases.

    Since we are struggling to find a resolution and I guess we are both based in the UK. Please click on my profile then send your e-mail to my LinkedIn account. (You will have to join LinkedIn to do this).

    As always any development work needs to performed in a test environment first before releasing to live.

    I sincerely hope we can find an effective resolution together.

    Kind Regards,

    Kieran.


    If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/kieranpatrickwood
    Friday, February 4, 2011 8:46 PM
  • Hi Steve,

    Also try; -  

    set dateformat dmy
    

    http://msdn.microsoft.com/en-us/library/ms189491.aspx

    Kind Regards,

    Kieran


    If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/kieranpatrickwood
    Monday, February 14, 2011 11:54 AM
  • Hi Kieran

    Apologies for the slow response, I have very busy recently. I have tried the above code but again, although the syntax is accepted and I get a confimation (e.g. Changed language setting to British.), nothing happens. One thing I have noticed however, is that when running the app in VS2010 debug from my Win 7 laptop (using my laptop's local web server rather than the remote server), the dates all display 100% correctly. I assume this rules out differences in versions being the cause?

    Thanks

    Steve

    Wednesday, February 16, 2011 5:15 PM
  • Hi Steve,

    I think this takes us back to Tom's recommendation given earlier. So I think you need to focus on the .Net code which sets up the connection to SQL Server and sends SQL statements, etc to SQL Server from your application.

    So I recommend create a post within a forum within the following url; - http://msdn.microsoft.com/en-us/netframework/default

    Narrow down the problem domain by creating a tiny application consisting of the smallest possible block of .Net code and embedded SQL which generates the date formatting issue on your remote server but does not generate the date formatting issue on your local server. When you have done this cut and paste your anonymized code into a question within a .Net Framework Forum.

    Kind Regards,

    Kieran.


    If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/kieranpatrickwood
    Saturday, February 19, 2011 8:58 AM