Date Issue

Hi,
I have two development PC's. Each PC has SQL Server 2000 and Visual Studio
2005 installed. One has Microsoft Office 2003 installed.
Both have exactly the same regional settings.
When I run a procedure in VB.Net 2005 to store date information in one
database the date is stored mm/dd/yyyy. In the other it is stored as
dd/mm/yyyy.
I want to store the date as dd/mm/yyyy.
I have tried to format the date within the SQL insert - when I debug the
format is dd/mm/yyyy but once the record is saved it reverts to mm/dd/yyyy.
I cannot find any option in SQL Server to format dates.
Any ideas?
ThanksDarkman
When you store dates use YYYYMMDD , and if you want to display dates on the
client , use formating on the client
http://www.karaszi.com/SQLServer/info_datetime.asp
"Darkman" <Darkman@.discussions.microsoft.com> wrote in message
news:38B34B6C-C90C-49AD-BDE2-7C30E7624FB7@.microsoft.com...
> Hi,
> I have two development PC's. Each PC has SQL Server 2000 and Visual Studio
> 2005 installed. One has Microsoft Office 2003 installed.
> Both have exactly the same regional settings.
> When I run a procedure in VB.Net 2005 to store date information in one
> database the date is stored mm/dd/yyyy. In the other it is stored as
> dd/mm/yyyy.
> I want to store the date as dd/mm/yyyy.
> I have tried to format the date within the SQL insert - when I debug the
> format is dd/mm/yyyy but once the record is saved it reverts to
> mm/dd/yyyy.
> I cannot find any option in SQL Server to format dates.
> Any ideas?
> Thanks
>|||Two things:
1) I also store the time. So the stored value is:
dd/MM/yyyy HH:mm
2) I would still like to know why the two different installations have
different date formats.
"Uri Dimant" wrote:
> Darkman
> When you store dates use YYYYMMDD , and if you want to display dates on the
> client , use formating on the client
> http://www.karaszi.com/SQLServer/info_datetime.asp
>
>
> "Darkman" <Darkman@.discussions.microsoft.com> wrote in message
> news:38B34B6C-C90C-49AD-BDE2-7C30E7624FB7@.microsoft.com...
> > Hi,
> >
> > I have two development PC's. Each PC has SQL Server 2000 and Visual Studio
> > 2005 installed. One has Microsoft Office 2003 installed.
> >
> > Both have exactly the same regional settings.
> >
> > When I run a procedure in VB.Net 2005 to store date information in one
> > database the date is stored mm/dd/yyyy. In the other it is stored as
> > dd/mm/yyyy.
> >
> > I want to store the date as dd/mm/yyyy.
> >
> > I have tried to format the date within the SQL insert - when I debug the
> > format is dd/mm/yyyy but once the record is saved it reverts to
> > mm/dd/yyyy.
> >
> > I cannot find any option in SQL Server to format dates.
> >
> > Any ideas?
> >
> > Thanks
> >
>
>|||Darkman
Why? I do not know , but the matter is to use YYYYMMDD HH:MM format to
store dates
SELECT CAST('20080101 10:23' AS DATETIME)
"Darkman" <Darkman@.discussions.microsoft.com> wrote in message
news:470D14C7-893D-49E7-9B2B-A26C69E11FC1@.microsoft.com...
> Two things:
> 1) I also store the time. So the stored value is:
> dd/MM/yyyy HH:mm
> 2) I would still like to know why the two different installations have
> different date formats.
>
> "Uri Dimant" wrote:
>> Darkman
>> When you store dates use YYYYMMDD , and if you want to display dates on
>> the
>> client , use formating on the client
>> http://www.karaszi.com/SQLServer/info_datetime.asp
>>
>>
>> "Darkman" <Darkman@.discussions.microsoft.com> wrote in message
>> news:38B34B6C-C90C-49AD-BDE2-7C30E7624FB7@.microsoft.com...
>> > Hi,
>> >
>> > I have two development PC's. Each PC has SQL Server 2000 and Visual
>> > Studio
>> > 2005 installed. One has Microsoft Office 2003 installed.
>> >
>> > Both have exactly the same regional settings.
>> >
>> > When I run a procedure in VB.Net 2005 to store date information in one
>> > database the date is stored mm/dd/yyyy. In the other it is stored as
>> > dd/mm/yyyy.
>> >
>> > I want to store the date as dd/mm/yyyy.
>> >
>> > I have tried to format the date within the SQL insert - when I debug
>> > the
>> > format is dd/mm/yyyy but once the record is saved it reverts to
>> > mm/dd/yyyy.
>> >
>> > I cannot find any option in SQL Server to format dates.
>> >
>> > Any ideas?
>> >
>> > Thanks
>> >
>>|||> I have tried to format the date within the SQL insert - when I debug the
> format is dd/mm/yyyy but once the record is saved it reverts to
> mm/dd/yyyy.
What does DBCC USEROPTIONS return? Different DATEFORMAT settings on the 2
machines would explain why identical datetime strings are interpreted
differently.
In any case, I strongly suggest you use parameterized SQL Statements.
Parameterized statements are more secure, do not require you to format date
strings or double-up quotes and also promote query plan reuse. Example
below.
'create parameterized command
Dim insertCommand AS SqlCommand = _
New SqlCommand( _
"INSERT INTO dbo.MyTable (MyDateTime) " + _
"VALUES(@.MyDateTime)", connection)
'create parameter and set value
insertCommand.Parameters.Add( _
"@.MyDateTime", _
SqlDbType.DateTime).Value = DateTime.Now
'execute command
insertCommand.ExecuteNonQuery()
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Darkman" <Darkman@.discussions.microsoft.com> wrote in message
news:38B34B6C-C90C-49AD-BDE2-7C30E7624FB7@.microsoft.com...
> Hi,
> I have two development PC's. Each PC has SQL Server 2000 and Visual Studio
> 2005 installed. One has Microsoft Office 2003 installed.
> Both have exactly the same regional settings.
> When I run a procedure in VB.Net 2005 to store date information in one
> database the date is stored mm/dd/yyyy. In the other it is stored as
> dd/mm/yyyy.
> I want to store the date as dd/mm/yyyy.
> I have tried to format the date within the SQL insert - when I debug the
> format is dd/mm/yyyy but once the record is saved it reverts to
> mm/dd/yyyy.
> I cannot find any option in SQL Server to format dates.
> Any ideas?
> Thanks
>|||If it is a datetime column then it is not "stored" that way at all. It is
how the client tools are showing the dates (or perhaps how the server
interprets the literals that are being passed to it). This can be affected
by regional settings on the client or server, dateformat or language
settings in SQL Server, etc.
On each server, try storing a date like 2007-11-17. If it is because of the
way the date literal is being interpreted, one server should return an
error.
You should never pass strings manually formatted as d/m/y or m/d/y. Always
use ISO formats like YYYYMMDD or YYYY-MM-DDTHH:MM:SS. These are the only
two formats that are guaranteed "safe" in SQL Server. Anything else can be
wrongly interpreted due to several variables, including those listed above.
It is easy to demonstrate this, both using m/d/y and d/m/y literal formats:
SET LANGUAGE BRITISH
SELECT CONVERT(DATETIME, '11/17/2007')
GO
SET LANGUAGE FRENCH
SELECT CONVERT(DATETIME, '11/17/2007')
GO
SET DATEFORMAT DMY
SELECT CONVERT(DATETIME, '11/17/2007')
GO
SET LANGUAGE ENGLISH
SELECT CONVERT(DATETIME, '17/11/2007')
GO
Please read up on Tibor's article:
http://www.karaszi.com/SQLServer/info_datetime.asp
It should reinforce why you should always use the above described formats
when passing date literals. Or, better yet, use parameterized statements
and strongly typed variables when you can, as Dan suggested.
"Darkman" <Darkman@.discussions.microsoft.com> wrote in message
news:38B34B6C-C90C-49AD-BDE2-7C30E7624FB7@.microsoft.com...
> Hi,
> I have two development PC's. Each PC has SQL Server 2000 and Visual Studio
> 2005 installed. One has Microsoft Office 2003 installed.
> Both have exactly the same regional settings.
> When I run a procedure in VB.Net 2005 to store date information in one
> database the date is stored mm/dd/yyyy. In the other it is stored as
> dd/mm/yyyy.
> I want to store the date as dd/mm/yyyy.
> I have tried to format the date within the SQL insert - when I debug the
> format is dd/mm/yyyy but once the record is saved it reverts to
> mm/dd/yyyy.
> I cannot find any option in SQL Server to format dates.
> Any ideas?
> Thanks
>