DateTime question

I'm trying to update a datetime field in my database with the current
date and time. The following command inserts the date as 1/13/06 when
most systems recognize 38728 as 1/11/06. Why does sql server 2005
increase this value by two days. The code that uses this database is
written in C# and makes heavy use of DateTime especially DateTime.Now,
which thinks 38728 is 1/11/06 so I'm not looking for a different way to
add this record, I want sql server to understand that 38728 is 1/11/06
and not 1/13/06.
Please help!
INSERT INTO [dbo].[Test]
([EID]
,[modifiedBy]
,[modifiedOn])
VALUES
(9999,
'TestHarness',
38728)What result does the following give you :-
select @.@.datefirst
Try SET @.@.DateFirst (two less than the result from above) before running
your insert
--
HTH. Ryan
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegroups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>|||You don't use the product correctly. You express datetimes in SQL Server as a string, not as a
number. Unfortunately, SQL Server accepts a number (implicit datatype conversion) and thereby
exposes the internals of the product. And that happens to be different from some other systems, as
you have noticed. You cannot change the behavior in this regard. Check out:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegroups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>|||Why not just take the c# datetime and convert to SqlDateTime and store that?
--
William Stacey [MVP]
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegroups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>sql