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...
> > 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
"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...
>> 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...
>> > 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 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:
> > 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
>|||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...
>> 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
>|||Then there's something wrong. I tried your code and got the correct values:
1, Nobody, Joe, B, 2005-10-03 15:22:00
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: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...
> 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
>|||The table in question is set up something similar to this
--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
)
---
Then, when using the following insert query I receive 1900-01-01 in the
DateStamp field.
--INSERT QUERY--
INSERT INTO Test (Lname, Fname, MName)
VALUES ('Nobody', 'Joe', 'B')
---
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eaUbF3EyFHA.916@.TK2MSFTNGP10.phx.gbl...
> 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...
> >> 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...
> >> > 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
> >> >
> >> >
> >>
> >>
> >
> >
>|||Yeah...I tried that too. From just query Analyzer i ran SELECT GetDate()
and received the current date.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e2pnk%23EyFHA.2348@.TK2MSFTNGP15.phx.gbl...
> 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...
> >> 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
> >>
> >
> >
>|||Yeup....There is defiantly something amiss here. A while back I had a
similar problem with the a similar setup only this time NULLs were allowed
on the DateStamp field and instead of 1900-01-01 It would end up NULL.
However, this problem, for whatever reason, eventually fixed it self before
I had the time to troubleshoot it.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uXLId$EyFHA.916@.TK2MSFTNGP10.phx.gbl...
> Then there's something wrong. I tried your code and got the correct
values:
>
> 1, Nobody, Joe, B, 2005-10-03 15:22:00
>
> --
> 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: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...
> > 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
> >
>|||Scott,
Try using CURRENT_TIMESTAMP instead.
HTH
Jerry
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:OnQnpCFyFHA.908@.tk2msftngp13.phx.gbl...
> Yeup....There is defiantly something amiss here. A while back I had a
> similar problem with the a similar setup only this time NULLs were allowed
> on the DateStamp field and instead of 1900-01-01 It would end up NULL.
> However, this problem, for whatever reason, eventually fixed it self
> before
> I had the time to troubleshoot it.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uXLId$EyFHA.916@.TK2MSFTNGP10.phx.gbl...
>> Then there's something wrong. I tried your code and got the correct
> values:
>>
>> 1, Nobody, Joe, B, 2005-10-03 15:22:00
>>
>> --
>> 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: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...
>> > 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
>> >
>>
>|||See if you have a trigger that "removes" the date part...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:OnQnpCFyFHA.908@.tk2msftngp13.phx.gbl...
> Yeup....There is defiantly something amiss here. A while back I had a
> similar problem with the a similar setup only this time NULLs were allowed
> on the DateStamp field and instead of 1900-01-01 It would end up NULL.
> However, this problem, for whatever reason, eventually fixed it self before
> I had the time to troubleshoot it.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uXLId$EyFHA.916@.TK2MSFTNGP10.phx.gbl...
>> Then there's something wrong. I tried your code and got the correct
> values:
>>
>> 1, Nobody, Joe, B, 2005-10-03 15:22:00
>>
>> --
>> 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: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...
>> > 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
>> >
>>
>|||There are no triggers associated with the table in question.
-Scott
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OYi5VqFyFHA.3720@.TK2MSFTNGP14.phx.gbl...
> See if you have a trigger that "removes" the date part...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:OnQnpCFyFHA.908@.tk2msftngp13.phx.gbl...
> > Yeup....There is defiantly something amiss here. A while back I had a
> > similar problem with the a similar setup only this time NULLs were
allowed
> > on the DateStamp field and instead of 1900-01-01 It would end up NULL.
> > However, this problem, for whatever reason, eventually fixed it self
before
> > I had the time to troubleshoot it.
> >
> >
> > "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> > news:uXLId$EyFHA.916@.TK2MSFTNGP10.phx.gbl...
> >> Then there's something wrong. I tried your code and got the correct
> > values:
> >>
> >>
> >> 1, Nobody, Joe, B, 2005-10-03 15:22:00
> >>
> >>
> >> --
> >> 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: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...
> >> > 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
> >> >
> >>
> >>
> >
> >
>|||No dice.
-scott
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23q6CdEFyFHA.1132@.TK2MSFTNGP10.phx.gbl...
> Scott,
> Try using CURRENT_TIMESTAMP instead.
> HTH
> Jerry
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:OnQnpCFyFHA.908@.tk2msftngp13.phx.gbl...
> > Yeup....There is defiantly something amiss here. A while back I had a
> > similar problem with the a similar setup only this time NULLs were
allowed
> > on the DateStamp field and instead of 1900-01-01 It would end up NULL.
> > However, this problem, for whatever reason, eventually fixed it self
> > before
> > I had the time to troubleshoot it.
> >
> >
> > "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> > news:uXLId$EyFHA.916@.TK2MSFTNGP10.phx.gbl...
> >> Then there's something wrong. I tried your code and got the correct
> > values:
> >>
> >>
> >> 1, Nobody, Joe, B, 2005-10-03 15:22:00
> >>
> >>
> >> --
> >> 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: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...
> >> > 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
> >> >
> >>
> >>
> >
> >
>|||Scott Elgram wrote:
> Yeah...I tried that too. From just query Analyzer i ran SELECT
> GetDate() and received the current date.
>
Verify the actual SQL Statement running on the table from Profiler and
make sure it looks correct.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I guess that we need to see a repro, then. Hard to say anything if we can't reproduce the behavior.
Unless the application that inserts the data removes the date part, i.e., the default isn't used?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:O0sB26FyFHA.2212@.TK2MSFTNGP15.phx.gbl...
> There are no triggers associated with the table in question.
> -Scott
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OYi5VqFyFHA.3720@.TK2MSFTNGP14.phx.gbl...
>> See if you have a trigger that "removes" the date part...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
>> news:OnQnpCFyFHA.908@.tk2msftngp13.phx.gbl...
>> > Yeup....There is defiantly something amiss here. A while back I had a
>> > similar problem with the a similar setup only this time NULLs were
> allowed
>> > on the DateStamp field and instead of 1900-01-01 It would end up NULL.
>> > However, this problem, for whatever reason, eventually fixed it self
> before
>> > I had the time to troubleshoot it.
>> >
>> >
>> > "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> > news:uXLId$EyFHA.916@.TK2MSFTNGP10.phx.gbl...
>> >> Then there's something wrong. I tried your code and got the correct
>> > values:
>> >>
>> >>
>> >> 1, Nobody, Joe, B, 2005-10-03 15:22:00
>> >>
>> >>
>> >> --
>> >> 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: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...
>> >> > 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
>> >> >
>> >>
>> >>
>> >
>> >
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment