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
Showing posts with label inserts. Show all posts
Showing posts with label inserts. Show all posts
Wednesday, March 21, 2012
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 conver
sion) 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)
>
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 conver
sion) 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)
>
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.googlegr oups.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.googlegr oups.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.googlegr oups.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
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.googlegr oups.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.googlegr oups.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.googlegr oups.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
Tuesday, February 14, 2012
Date/Time Stamp
Good day,
Is there a way to add a field in a SQL table that automatically inserts a
date/time stamp when the record is created?
Thank you in advance...On Feb 27, 10:38=A0pm, PsyberFox <Psyber...@.discussions.microsoft.com>
wrote:
> Good day,
> Is there a way to add a field in a SQL table that automatically inserts a
> date/time stamp when the record is created?
> Thank you in advance...
You can do it by writing a an insert trigger on table.|||... or having a default value for the column. Of course, a default can be overridden by the INSERT
statement.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<vinodkus@.gmail.com> wrote in message
news:746d44f8-b988-4950-b74a-b5d152515abc@.62g2000hsn.googlegroups.com...
On Feb 27, 10:38 pm, PsyberFox <Psyber...@.discussions.microsoft.com>
wrote:
> Good day,
> Is there a way to add a field in a SQL table that automatically inserts a
> date/time stamp when the record is created?
> Thank you in advance...
You can do it by writing a an insert trigger on table.|||"PsyberFox" <PsyberFox@.discussions.microsoft.com> wrote in message
news:9B7F88F8-B885-4A28-979B-9B43385D26FF@.microsoft.com...
> Good day,
> Is there a way to add a field in a SQL table that automatically inserts a
> date/time stamp when the record is created?
Just create it with a default of GETDATE() and make sure your procedure(s)
that insert into it don't specify a value for it
CREATE TABLE test (
dtmTimeStamp DATETIME DEFAULT GETDATE(),
[more columns]
)
If you need it to be 'secure' then as someone else mentioned you'll have to
create triggers to keep the value fixed and/or make sure an explicit value
can't be inserted.
Is there a way to add a field in a SQL table that automatically inserts a
date/time stamp when the record is created?
Thank you in advance...On Feb 27, 10:38=A0pm, PsyberFox <Psyber...@.discussions.microsoft.com>
wrote:
> Good day,
> Is there a way to add a field in a SQL table that automatically inserts a
> date/time stamp when the record is created?
> Thank you in advance...
You can do it by writing a an insert trigger on table.|||... or having a default value for the column. Of course, a default can be overridden by the INSERT
statement.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<vinodkus@.gmail.com> wrote in message
news:746d44f8-b988-4950-b74a-b5d152515abc@.62g2000hsn.googlegroups.com...
On Feb 27, 10:38 pm, PsyberFox <Psyber...@.discussions.microsoft.com>
wrote:
> Good day,
> Is there a way to add a field in a SQL table that automatically inserts a
> date/time stamp when the record is created?
> Thank you in advance...
You can do it by writing a an insert trigger on table.|||"PsyberFox" <PsyberFox@.discussions.microsoft.com> wrote in message
news:9B7F88F8-B885-4A28-979B-9B43385D26FF@.microsoft.com...
> Good day,
> Is there a way to add a field in a SQL table that automatically inserts a
> date/time stamp when the record is created?
Just create it with a default of GETDATE() and make sure your procedure(s)
that insert into it don't specify a value for it
CREATE TABLE test (
dtmTimeStamp DATETIME DEFAULT GETDATE(),
[more columns]
)
If you need it to be 'secure' then as someone else mentioned you'll have to
create triggers to keep the value fixed and/or make sure an explicit value
can't be inserted.
Date/Time Stamp
Good day,
Is there a way to add a field in a SQL table that automatically inserts a
date/time stamp when the record is created?
Thank you in advance...
On Feb 27, 10:38Xpm, PsyberFox <Psyber...@.discussions.microsoft.com>
wrote:
> Good day,
> Is there a way to add a field in a SQL table that automatically inserts a
> date/time stamp when the record is created?
> Thank you in advance...
You can do it by writing a an insert trigger on table.
|||"PsyberFox" <PsyberFox@.discussions.microsoft.com> wrote in message
news:9B7F88F8-B885-4A28-979B-9B43385D26FF@.microsoft.com...
> Good day,
> Is there a way to add a field in a SQL table that automatically inserts a
> date/time stamp when the record is created?
Just create it with a default of GETDATE() and make sure your procedure(s)
that insert into it don't specify a value for it
CREATE TABLE test (
dtmTimeStamp DATETIME DEFAULT GETDATE(),
[more columns]
)
If you need it to be 'secure' then as someone else mentioned you'll have to
create triggers to keep the value fixed and/or make sure an explicit value
can't be inserted.
Is there a way to add a field in a SQL table that automatically inserts a
date/time stamp when the record is created?
Thank you in advance...
On Feb 27, 10:38Xpm, PsyberFox <Psyber...@.discussions.microsoft.com>
wrote:
> Good day,
> Is there a way to add a field in a SQL table that automatically inserts a
> date/time stamp when the record is created?
> Thank you in advance...
You can do it by writing a an insert trigger on table.
|||"PsyberFox" <PsyberFox@.discussions.microsoft.com> wrote in message
news:9B7F88F8-B885-4A28-979B-9B43385D26FF@.microsoft.com...
> Good day,
> Is there a way to add a field in a SQL table that automatically inserts a
> date/time stamp when the record is created?
Just create it with a default of GETDATE() and make sure your procedure(s)
that insert into it don't specify a value for it
CREATE TABLE test (
dtmTimeStamp DATETIME DEFAULT GETDATE(),
[more columns]
)
If you need it to be 'secure' then as someone else mentioned you'll have to
create triggers to keep the value fixed and/or make sure an explicit value
can't be inserted.
Subscribe to:
Posts (Atom)