Sunday, February 19, 2012

Default value GetDate() on column

hello,
I have an interesting problem here.
In a table I have a SmallDateTime column with the default value
GetDate(). Everything was just dandy for a while but now for every record
added to that table the field shows 1900-01-01. If I do a "SELECT
GetDate()" in the SQL query analyzer it works but in that one field it
always shows 1900-01-01.
Any help would be greatly appreciated.
-ScottScott,
Ensure the end-users are supplying a date as well as a time or it will
default to 1900-01-01 for the date.
See the following as a test:
CREATE TABLE DT
(DTVAL SMALLDATETIME DEFAULT GETDATE())
GO
INSERT DT
DEFAULT VALUES
INSERT DT
VALUES ('9:30')
GO
SELECT * FROM DT
HTH
Jerry
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:uFDzqSEyFHA.612@.TK2MSFTNGP10.phx.gbl...
> hello,
> I have an interesting problem here.
> In a table I have a SmallDateTime column with the default value
> GetDate(). Everything was just dandy for a while but now for every record
> added to that table the field shows 1900-01-01. If I do a "SELECT
> GetDate()" in the SQL query analyzer it works but in that one field it
> always shows 1900-01-01.
> Any help would be greatly appreciated.
> --
> -Scott
>|||It sounds like either a trigger has been added to the table, overriding the
default, or that insert's are being done with an explicit value of
1900-01-01 for the column. If there is no trigger, then trace the app,
using the Profiler.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:uFDzqSEyFHA.612@.TK2MSFTNGP10.phx.gbl...
hello,
I have an interesting problem here.
In a table I have a SmallDateTime column with the default value
GetDate(). Everything was just dandy for a while but now for every record
added to that table the field shows 1900-01-01. If I do a "SELECT
GetDate()" in the SQL query analyzer it works but in that one field it
always shows 1900-01-01.
Any help would be greatly appreciated.
-Scott|||The table in question here is being used as a log for updates made via
website. When the user hits save it runs two queries...the first saves the
data into the "Live" table and the second copies what was saved into the log
table. The purpose for this is that with the "Live" table information is
overwritten but in the log table information is not overwritten.....every
time the user hits save a new record is created in the log table where the
default value of GetDate() in that log table acts as a stamp date for when
the user hits save.
I understand a trigger on the "Live" would sound like a better option
than running two queries but the "Live" table is edited by both staff and
website users and we only wished to log the changes made by the website
users. The problem is that for the longest time the table was running just
fine, default value and all. It wasn't until just recently that I noticed
all the default values of GetDate() were 1900-01-01. This is even true for
many days ago. At first I though it was a fluke and proceeded to inspect
the code of the website...that all checked out. Then I thought maybe
something weird with the filed so I added an additional filed with the same
default value of GetDate(). Even still I got 1900-01-01. Ok then, maybe it
is the table...so I recreated a similar table and this time used the query
analyzer but still, I got a date of 1900-01-01. So now I'm stumped cause
this seems to be the only place it is happening. Other tables that are set
up with similar default values are putting in the current dates.
-Scott
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eLtUzXEyFHA.2212@.TK2MSFTNGP15.phx.gbl...
> Scott,
> Ensure the end-users are supplying a date as well as a time or it will
> default to 1900-01-01 for the date.
> See the following as a test:
> CREATE TABLE DT
> (DTVAL SMALLDATETIME DEFAULT GETDATE())
> GO
> INSERT DT
> DEFAULT VALUES
> INSERT DT
> VALUES ('9:30')
> GO
> SELECT * FROM DT
> HTH
> Jerry
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:uFDzqSEyFHA.612@.TK2MSFTNGP10.phx.gbl...
record[vbcol=seagreen]
>|||The table in question here is being used as a log for updates made via
website. When the user hits save it runs two queries...the first saves the
data into the "Live" table and the second copies what was saved into the log
table. The purpose for this is that with the "Live" table information is
overwritten but in the log table information is not overwritten.....every
time the user hits save a new record is created in the log table where the
default value of GetDate() in that log table acts as a stamp date for when
the user hits save.
I understand a trigger on the "Live" would sound like a better option
than running two queries but the "Live" table is edited by both staff and
website users and we only wished to log the changes made by the website
users. The problem is that for the longest time the table was running just
fine, default value and all. It wasn't until just recently that I noticed
all the default values of GetDate() were 1900-01-01. This is even true for
many days ago. At first I though it was a fluke and proceeded to inspect
the code of the website...that all checked out. Then I thought maybe
something weird with the filed so I added an additional filed with the same
default value of GetDate(). Even still I got 1900-01-01. Ok then, maybe it
is the table...so I recreated a similar table and this time used the query
analyzer but still, I got a date of 1900-01-01. So now I'm stumped cause
this seems to be the only place it is happening. Other tables that are set
up with similar default values are putting in the current dates.
-Scott
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:u0wfpZEyFHA.2312@.TK2MSFTNGP14.phx.gbl...
> It sounds like either a trigger has been added to the table, overriding
the
> default, or that insert's are being done with an explicit value of
> 1900-01-01 for the column. If there is no trigger, then trace the app,
> using the Profiler.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:uFDzqSEyFHA.612@.TK2MSFTNGP10.phx.gbl...
> hello,
> I have an interesting problem here.
> In a table I have a SmallDateTime column with the default value
> GetDate(). Everything was just dandy for a while but now for every record
> added to that table the field shows 1900-01-01. If I do a "SELECT
> GetDate()" in the SQL query analyzer it works but in that one field it
> always shows 1900-01-01.
> Any help would be greatly appreciated.
> --
> -Scott
>|||Scott Elgram wrote:
> hello,
> I have an interesting problem here.
> In a table I have a SmallDateTime column with the default value
> GetDate(). Everything was just dandy for a while but now for every
> record added to that table the field shows 1900-01-01. If I do a
> "SELECT GetDate()" in the SQL query analyzer it works but in that one
> field it always shows 1900-01-01.
> Any help would be greatly appreciated.
Could you show us the CREATE TABLE definition, any triggers on the
table, and an actual insert statement that is causing the problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:u0wfpZEyFHA.2312@.TK2MSFTNGP14.phx.gbl...
> It sounds like either a trigger has been added to the table, overriding
> the
> default, or that insert's are being done with an explicit value of
> 1900-01-01 for the column. If there is no trigger, then trace the app,
> using the Profiler.
or an explicit value of an empty string (which is my guess)|||Scott,
Using the same example from prior post:
INSERT DT
VALUES ('') --WILL PRODUCE 1900-01-01 00:00:00
Is there a time entry for the date?
INSERT DT
VALUES ('9:30') --WILL PRODUCE 1900-01-01 09:30:00
Are you checking the integrity of the data entered? Might try ISDATE().
HTH
Jerry
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:e2IYxqEyFHA.624@.TK2MSFTNGP11.phx.gbl...
> The table in question here is being used as a log for updates made via
> website. When the user hits save it runs two queries...the first saves
> the
> data into the "Live" table and the second copies what was saved into the
> log
> table. The purpose for this is that with the "Live" table information is
> overwritten but in the log table information is not overwritten.....every
> time the user hits save a new record is created in the log table where the
> default value of GetDate() in that log table acts as a stamp date for when
> the user hits save.
> I understand a trigger on the "Live" would sound like a better option
> than running two queries but the "Live" table is edited by both staff and
> website users and we only wished to log the changes made by the website
> users. The problem is that for the longest time the table was running
> just
> fine, default value and all. It wasn't until just recently that I noticed
> all the default values of GetDate() were 1900-01-01. This is even true
> for
> many days ago. At first I though it was a fluke and proceeded to inspect
> the code of the website...that all checked out. Then I thought maybe
> something weird with the filed so I added an additional filed with the
> same
> default value of GetDate(). Even still I got 1900-01-01. Ok then, maybe
> it
> is the table...so I recreated a similar table and this time used the
> query
> analyzer but still, I got a date of 1900-01-01. So now I'm stumped cause
> this seems to be the only place it is happening. Other tables that are
> set
> up with similar default values are putting in the current dates.
> -Scott
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eLtUzXEyFHA.2212@.TK2MSFTNGP15.phx.gbl...
> record
>|||The actual table is very large with many fields so I tried this with the
same result.
--CREATE TABLE--
CREATE TABLE Test (
[ID] int IDENTITY (1, 1) NOT NULL,
[Lname] varchar(25) NOT NULL,
[Fname] varchar(25) NOT NULL,
[Mname] varchar(25) NULL,
[DateStamp] SmalldateTime DEFAULT GetDate() NOT NULL
)
---
--INSERT QUERY--
INSERT INTO Test (Lname, Fname, MName)
VALUES ('Nobody', 'Joe', 'B')
---
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:u6a1utEyFHA.720@.TK2MSFTNGP15.phx.gbl...
> Scott Elgram wrote:
> Could you show us the CREATE TABLE definition, any triggers on the
> table, and an actual insert statement that is causing the problem.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Scott,
What happens if you just run:
SELECT GETDATE()
Might check time/regional settings.
HTH
Jerry
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:eX4328EyFHA.700@.TK2MSFTNGP11.phx.gbl...
> The actual table is very large with many fields so I tried this with the
> same result.
> --CREATE TABLE--
> CREATE TABLE Test (
> [ID] int IDENTITY (1, 1) NOT NULL,
> [Lname] varchar(25) NOT NULL,
> [Fname] varchar(25) NOT NULL,
> [Mname] varchar(25) NULL,
> [DateStamp] SmalldateTime DEFAULT GetDate() NOT NULL
> )
> ---
> --INSERT QUERY--
> INSERT INTO Test (Lname, Fname, MName)
> VALUES ('Nobody', 'Joe', 'B')
> ---
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:u6a1utEyFHA.720@.TK2MSFTNGP15.phx.gbl...
>

No comments:

Post a Comment