datetime Format SQL 2005

Hi all,

Apologies if this has cropped up before...but I've searched the forums here and not found an answer to my specific problem.

I've just starting hosting my site with discountasp.net and have an online bookings system as part of my site which relies heavily on the datetime format in SQL. As I'm from the UK my SQL 2005 server settings (in my local dev environment) format the date like this: dd/mm/yyyy. However, whenever I grab a datetime from the database of my live site it is returning the date in U.S format (I think?) mm/dd/yyyy (also, sometimes if the day or month is less than 10 it truncates the 0 - e.g. 5 instead of 05 and I want to keep a full date e.g. 05/05/2005 rather than 5/5/2005)

This is causing me problems when submitting new booking requests or replaying dates that have already been booked. Anyone know how I can format the datetime to format as dd/mm/yyyy from the database. I've googled and searched to my heart's content and tried SET LANGUAGE and various CONVERTS etc. but have had no luck.

Any help would be gratefully appreciated.

Thanks,

Matt

 

Hi,
This is a very good article on how to address that:
http://weblogs.sqlteam.com/jeffs/archive/2007/04/13/format-date-sql-server.aspx
All the best,
Mark

 

Thanks for the reply - that site was useful. Got me to the solution after a bit more searching. Changing the front-end presentation is the way to go.

Example here (using the CultureInfo class) as a parameter in the DateTime.ToString(); method allows us to choose a culture and the DateTime will be formatted according to that culture.

Here's an example of outputting just the date from a DateTime object in English format (dd/mm/yyyy):

myDate.SelectedDate.ToString("d", new System.Globalization.CultureInfo("en-NZ");

(If you wanted the whole DateTime - date and time - just omit the "d" parameter).

"en-NZ" is a culture code which determines the format of your DateTime output.

A list of culture codes to construct the CultureInfo class can be found here: http://www.dotnetindex.com/read.asp?articleID=34. There's a table listing the culture codes and the date formats associated with them.

Hope this is helpful to someone along the line someday.

Matt

 

...Awesome, it's always great when anyone re-posts with solutions/followups.
All the best,
Mark

 

Just one last note (I promise)

I found another easier way to solve this using String.Format()

string output = String.Format("{0:dd/MM/yyyy}", myDateTimeObject);

Works perfectly for my problem described above and avoids the more complex CultureInfo class solution described in my previous post.

Hope this helps someone - case closed!

Matt

 

...

Note: SQL Server 2008 changed the way we've had to do this, for years.
That won't help you right now but you will love the changes.
All the best,
Mark

 

UK Date ?

I too am located in the UK.

I recently transferred from the US data centre, to the UK data centre, and upgraded to SQL2005.
I am developing in VS2008 .NET 3.5 and using Telerik controls, with my data-source controls pointing to my new database located at DiscountASP UK data centre.
When I click the view in browser icon within the development environment, the VS2008 Environment Test Server opens and renders the site, with all dates displaying correctly, and all filters and date selectors working as expected.
I have checked that all culture settings are configured for UK, and display string formatting set appropriately.
The problem is that my application dates display in US format when my application is run from my UK based DiscountASP server.

I have checked the "Server Info" for my database from the control panel on the server, and discovered the following.

Version 9.00.4207.00
Product level SP3
Edition Standard Edition
Resource version 9.00.4207
Resource last update time Dec 17 2008 3:36PM
CLR version v2.0.50727
Language English (United States)

I have logged a Ticket with support, and have been informed that this has been escalated.

Why would a UK based SQL Server be configured for Language: English (United States) ?

 

Yes that is correct. Our date and time format is US English version but the time zone for our UK servers are set for the UK time zone. We choose the US English language because we are a US base company and it is our standard language for communicating with our customers.

 

I think it's still a good idea to format the dates at the presentation layer (using the examples in earlier posts) rather than the data itself or modifiying core SQL Server settings. That way your application isn't dependant on certain SQL server settings and will work in any situation.

Like I say, the String.Format() method described above has worked a treat for me.

 

Fix

Hello

I meant no offence regarding the Language being United States English.

I asked why the server language was displaying (United States) for the reason that this implies that the ‘Regional and Language Options’ of the computer’s Machine.config are also set to United States, and thus affecting the default date display format etc.

I wrongly assumed that as the servers are based in the UK, that the default ‘Regional and Language Options’ would be en-GB and not en-US.
Good article here: http://blogs.msdn.com/michkap/articles/279998.aspx

“requestEncoding
(Optional attribute).

Specifies the assumed encoding of each incoming request, including posted data and the query string.
If the request comes with a request header that contains an Accept-Charset attribute, it overrides this attribute in the configuration.

The default encoding is UTF-8, which is specified in the globalization section in the Machine.config file that was created when the .NET Framework was installed. If request encoding is not specified in a Machine.config or Web.config file, encoding defaults to the Regional Options locale setting for the computer.
http://msdn.microsoft.com/en-us/library/hy4kkhe0.aspx “

My FIX for the benefit of others.
I added the following into the <system> section of my web.config file.

<system.web>
<globalization
requestEncoding = "us-ascii"
responseEncoding = "iso-8859-1"
culture = "auto:en-GB"
/>

I did recieve a generous offer from DASP Support,stating that they would investigate my code/confiugration settings etc., in an effort to resolve the issue.

Thanks
Bill

 

...Wow, that's cool, thanks for posting.
Reminds me of the LCID setting in Classic ASP to resolve this same thing. ;-)
All the best,
Mark