April 22, 2009 at 3:45 pm
Hi All,
I had to install sql server 2005 express, a db and a software program I wrote in C# onto a German colleague's pc. Everything went fine however when he went to run the program he received a message about an invalid date string.
When I looked into it I noticed that on his SQL server the timestamp I used to save data was changed to a German format. Here in the US the timestamp was saved as mmddyyyy, however on his SQL server the format was changed to ddmmyyyy. Is there a way to change the way the date is displayed / stored in sql server 2005 express?
Thanks
April 22, 2009 at 5:22 pm
In SQL Server the datetime datatype (that is what you are using, right?) is stored as a double. This number is the number of days since 1900-01-01 where this date is our 0 date.
To verify this, you can run the following: SELECT CAST(0 AS datetime) and it will return 1900-01-01.
As long as you are using a date format that is not ambiguous, the dates will be inserted correctly. The two formats that are not ambiguous (in SQL Server) are:
YYYYMMDD HH:MM:SS.mmm
YYYY-MM-DDTHH:MM:SS.mmm
The way the date is displayed is determined by the server's date format and/or the client's date format. You can change those, but as long as the actual date is correct - why should you worry about it?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 22, 2009 at 6:27 pm
I am not sure this will work in this case, but look at this link.
April 23, 2009 at 9:30 am
Hi Thank you for the reply, the C# program connects to the db and brings the date in as it is displayed. With the display switching from us to european format the C# program does not recoginze the date format.
How can I change the way the date is displayed in the table? That would seem the easiest route. Otherwise I would have to write a routine to convert the date every time the db is accessed either to select data or save data. I am just not sure where to ad the necessary SQL statements to format the date. I have attached the table create SQL.. I imagine this is where I would make the change? Any help would be greatly appreciated.
USE [PROTOTYP_DB]
GO
/****** Object: Table [dbo].[Allocation] Script Date: 04/23/2009 11:22:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Allocation](
[Contract_NO] [char](8) NOT NULL,
[Parent_Company] [nchar](10) NULL,
[Contract_Description] [text] NULL,
[Date_Inception] [datetime] NULL,
[Date_Expiry] [datetime] NULL,
[Currency] [nchar](10) NULL,
[Annual_Premium_PD] [float] NULL,
[Annual_Premium_BI] [float] NULL,
[Min_Ave_Rate] [float] NULL,
CONSTRAINT [PK_Allocation] PRIMARY KEY CLUSTERED
(
[Contract_NO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
April 23, 2009 at 9:35 am
The C# should deal with the datetime datatype from the Db correctly if you store the data in datetime objects. For display purposes you should use the proper ToString... methods. They use the regional settings of the OS so the applications will display the date correctly. The key here is to maintain the date in your C# code as datetime objects.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 23, 2009 at 9:54 am
use the below code in your C# application:
DateTime.Now.ToString("G", DateTimeFormatInfo.InvariantInfo
for more information see help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply