sql delete multiple tables: getdate

Showing posts with label getdate. Show all posts

Friday, February 24, 2012

Default values do not get created in SQL 2005 tables

Got a table with fields CreatedBy and CreatedOn I set its Default Value or
Binding to Suser_Sname() and Getdate() respectively.
Testing inserting new records from my VB.NET user interface I find that
neither default values get populated, yet I have a trigger for insert or
delete that poluates the lastModifiedBy and last MOdifiedOn fields OK in
same table. This used to work fine in Server 2000 and VB6 or Vs Net 2003.
Any ideas why it no longer seems to work? It has to do with SQL server
itself I think. When I add a new record in the table itself. I get a message
saying that the new record has been added but that an error occurred when
the values were returned and in the selector column in the table view
there's a small red circle with an exclamation mark in it and the two
Created cells did not get populated. If I then click again on the execute
menu button, the two default value cells get populated OK and the
exclamation mark dissapears. It did not work that way in sql 2000, the
default values got populated on first try.
As test you can run this on the following table. On my system (the database
is on a Win2003 server, Its sql 2005 Standard and I'm running the Sql server
management studio on a Win Xp box, all the stuff has the latest service
packs) the behaviour is as described above.
/****** Object: Table [dbo].[tblCountries] Script Date: 01/02/2006 12:43:41
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblCountries](
[tblCountryId] [int] IDENTITY(1,1) NOT NULL,
[Country3LetterISOCode] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[CountryNameL1] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[CountryNameL2] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[CreatedBy] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_tblCountries_CreatedBy_1] DEFAULT (suser_sname()),
[CreatedOn] [datetime] NULL CONSTRAINT [DF_tblCountries_CreatedOn_1] DEFAULT
(getdate()),
[LastModifiedBy] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastModifiedOn] [datetime] NULL,
[ts] [timestamp] NULL,
CONSTRAINT [PK_tblCountries_1] PRIMARY KEY CLUSTERED
(
[tblCountryId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
I would really hope someone comes up with a solution. I can write triggers
that insert the default values, these seem to work OK. but It would be time
consuming for all my tables. I'd rather things worked as described in the
docs.<GGG>
Best regards and happy new year
BobHi Bob
It would have helped if you had supplied an insert statement for testing.
However, I noticed that most columns had either a default, allowed null, or
were an identity, so I tried the following insert:
INSERT INTO
[dbo]. [tblCountries](Country3LetterISOCode,Cou
ntryNameL1,CountryNameL2)
VALUES ('ABC', 'String1', 'String2')
The default values were inserted as expected. SQL Server 2005 is working
fine.
My guess is that the app is not building the insert statement correctly.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Bob" <bdufour@.sgiims.com> wrote in message
news:%23$Es1T8DGHA.2300@.TK2MSFTNGP15.phx.gbl...
> Got a table with fields CreatedBy and CreatedOn I set its Default Value or
> Binding to Suser_Sname() and Getdate() respectively.
> Testing inserting new records from my VB.NET user interface I find that
> neither default values get populated, yet I have a trigger for insert or
> delete that poluates the lastModifiedBy and last MOdifiedOn fields OK in
> same table. This used to work fine in Server 2000 and VB6 or Vs Net 2003.
> Any ideas why it no longer seems to work? It has to do with SQL server
> itself I think. When I add a new record in the table itself. I get a
> message saying that the new record has been added but that an error
> occurred when the values were returned and in the selector column in the
> table view there's a small red circle with an exclamation mark in it and
> the two Created cells did not get populated. If I then click again on the
> execute menu button, the two default value cells get populated OK and the
> exclamation mark dissapears. It did not work that way in sql 2000, the
> default values got populated on first try.
> As test you can run this on the following table. On my system (the
> database is on a Win2003 server, Its sql 2005 Standard and I'm running the
> Sql server management studio on a Win Xp box, all the stuff has the latest
> service packs) the behaviour is as described above.
> /****** Object: Table [dbo].[tblCountries] Script Date: 01/02/2006
> 12:43:41 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [dbo].[tblCountries](
> [tblCountryId] [int] IDENTITY(1,1) NOT NULL,
> [Country3LetterISOCode] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL,
> [CountryNameL1] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL,
> [CountryNameL2] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL,
> [CreatedBy] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_tblCountries_CreatedBy_1] DEFAULT (suser_sname()),
> [CreatedOn] [datetime] NULL CONSTRAINT [DF_tblCountries_CreatedOn_1]
> DEFAULT (getdate()),
> [LastModifiedBy] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL,
> [LastModifiedOn] [datetime] NULL,
> [ts] [timestamp] NULL,
> CONSTRAINT [PK_tblCountries_1] PRIMARY KEY CLUSTERED
> (
> [tblCountryId] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
>
> I would really hope someone comes up with a solution. I can write triggers
> that insert the default values, these seem to work OK. but It would be
> time consuming for all my tables. I'd rather things worked as described in
> the docs.<GGG>
> Best regards and happy new year
> Bob
>
>|||Kalen Thanks,
You are right the insert statement that you tried as a result does populated
the CreatedBy ANd CreatedOn cells.
But did you try just using the SQL management Studio Open the the table and
write the data directly in the table?
When you do that, you get the exclamation point as explained in my first
post, and that is the point.
If it does not work directly in the MStudio while you do direct data entry ,
it won't work with the default insert statements generated by Visual studio.
To simplify the problem, take the following simple table.
/****** Object: Table [dbo].[TestDefaults] Script Date: 01/02/2006 17:36:40
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestDefaults](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Data] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CreatedBy] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_TestDefaults_CreatedBy] DEFAULT (suser_sname()),
CONSTRAINT [PK_TestDefaults] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Use the script to create it.
Then just open the table in Studio manager and write any data in the cell
data, then move your cursor down one line. When you then move away from the
line. The CreatedBy cell does not appear to be populated and you get what I
described in my first post. Yet if you then close the table and open it
again you will see that the cell did indeed get populated. Now if you use
any automatically dataviewgrid generated in Visual studio 2005 and use that
form in Visual studio 2005 to insert new records, the CreateBy cell in the
table will not get populated. Do exactly the same with SQL server 2000
tables and the CreatedBy cell values will get populated.
The default insert command that gets generated by Visual Studio 2005 in the
table adapter where I first noticed the problem is the following. ( I took
it out of the autogenerated code for the table adapter being used.
Me._adapter.InsertCommand.CommandText = "INSERT INTO [tblCountries]
([Country3LetterISOCode], [CountryNameL1], [CountryNam"& _
"eL2], [CurrencyName], [CurrencySymbol], [CreatedBy], [CreatedOn],
[LastModifiedB"& _
"y], [LastModifiedOn]) VALUES (@.Country3LetterISOCode, @.CountryNameL1,
@.CountryNa"& _
"meL2, @.CurrencyName, @.CurrencySymbol, @.CreatedBy, @.CreatedOn,
@.LastModifiedBy, @."& _
"LastModifiedOn);"&Global.Microsoft.VisualBasic.ChrW(13)&Global.Microsoft.Vi
sualBasic.ChrW(10)&"SELECT
tblCountryId, Country3LetterISOCode, CountryNameL1, Cou"& _
"ntryNameL2, CurrencyName, CurrencySymbol, CreatedBy, CreatedOn,
LastModifiedBy, "& _
"LastModifiedOn, ts FROM tblCountries WHERE (tblCountryId =
SCOPE_IDENTITY())"
I think that what happens is that because the parameters @.CreatedBy and
@.CreatedOn must be getting passed as null values (I don't show them in or
affect them in my UI elements) by the Visual Studio program, they override
the default values specified in the table definitions, so the net result is
that when you look at the table after doing an insert with default Visual
studio generated code, you end up not getting any values in the cells set
with a default value in the table. I'm going to try to remove these default
fields from the table adapter in the Visual studio code and see if that
solves the problem.
Thanks for the tip of looking at the insert statement, its what got me
thinking on this possibility.
Regards,
Bob
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23HlOGf8DGHA.2088@.TK2MSFTNGP09.phx.gbl...
> Hi Bob
> It would have helped if you had supplied an insert statement for testing.
> However, I noticed that most columns had either a default, allowed null,
> or were an identity, so I tried the following insert:
>
> INSERT INTO
> [dbo]. [tblCountries](Country3LetterISOCode,Cou
ntryNameL1,CountryNameL2)
> VALUES ('ABC', 'String1', 'String2')
> The default values were inserted as expected. SQL Server 2005 is working
> fine.
> My guess is that the app is not building the insert statement correctly.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Bob" <bdufour@.sgiims.com> wrote in message
> news:%23$Es1T8DGHA.2300@.TK2MSFTNGP15.phx.gbl...
>
>|||Bob (bdufour@.sgiims.com) writes:
> You are right the insert statement that you tried as a result does
> populated the CreatedBy ANd CreatedOn cells.
> But did you try just using the SQL management Studio Open the the table
> and write the data directly in the table?
If I know Kalen well, I don't think she would try Open Table, unless
you explicitly said that the problem was with that function. If you
only say "insert", people like me and Kalen will think of an INSERT
statement, and not of Open Table. That's not a function we normally use.

> When you do that, you get the exclamation point as explained in my first
> post, and that is the point.
The pop-up says that the row was indeed inserted, but there was problems
of retrieving the value. If that is because of the IDENTITY column of
the default, I don't know. In any case, the issue you have is a tools
issue, not an issue with SQL Server itself.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Further Info
Just as I thought, when you use the standard table adapters and you
autogenerate your select insert update and delete statements using the
Visual studio 2005 UI. You have to be careful NOT to include in the fields
used by your table adapters any fields that have default values defined in
the database. If you have those fields included, the automatically generated
Insert statements will pass a NULL to the insert statement and that NULL
will overrode the default value defined.
Now that's a doozy :-) but in a weird sort of way it makes sense.
Hope all this f... around helps someone.
Happy new year,
Bob
"Bob" <bdufour@.sgiims.com> wrote in message
news:%238xRVF$DGHA.3856@.TK2MSFTNGP12.phx.gbl...
> Kalen Thanks,
> You are right the insert statement that you tried as a result does
> populated the CreatedBy ANd CreatedOn cells.
> But did you try just using the SQL management Studio Open the the table
> and write the data directly in the table?
> When you do that, you get the exclamation point as explained in my first
> post, and that is the point.
> If it does not work directly in the MStudio while you do direct data entry
> , it won't work with the default insert statements generated by Visual
> studio.
> To simplify the problem, take the following simple table.
> /****** Object: Table [dbo].[TestDefaults] Script Date: 01/02/2006
> 17:36:40 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [dbo].[TestDefaults](
> [ID] [int] IDENTITY(1,1) NOT NULL,
> [Data] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> [CreatedBy] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> CONSTRAINT [DF_TestDefaults_CreatedBy] DEFAULT (suser_sname()),
> CONSTRAINT [PK_TestDefaults] PRIMARY KEY CLUSTERED
> (
> [ID] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
> Use the script to create it.
> Then just open the table in Studio manager and write any data in the cell
> data, then move your cursor down one line. When you then move away from
> the line. The CreatedBy cell does not appear to be populated and you get
> what I described in my first post. Yet if you then close the table and
> open it again you will see that the cell did indeed get populated. Now if
> you use any automatically dataviewgrid generated in Visual studio 2005
> and use that form in Visual studio 2005 to insert new records, the
> CreateBy cell in the table will not get populated. Do exactly the same
> with SQL server 2000 tables and the CreatedBy cell values will get
> populated.
> The default insert command that gets generated by Visual Studio 2005 in
> the table adapter where I first noticed the problem is the following. ( I
> took it out of the autogenerated code for the table adapter being used.
> Me._adapter.InsertCommand.CommandText = "INSERT INTO [tblCountries]
> ([Country3LetterISOCode], [CountryNameL1], [CountryNam"& _
> "eL2], [CurrencyName], [CurrencySymbol], [CreatedBy], [CreatedOn],
> [LastModifiedB"& _
> "y], [LastModifiedOn]) VALUES (@.Country3LetterISOCode, @.CountryNameL1,
> @.CountryNa"& _
> "meL2, @.CurrencyName, @.CurrencySymbol, @.CreatedBy, @.CreatedOn,
> @.LastModifiedBy, @."& _
> "LastModifiedOn);"&Global.Microsoft.VisualBasic.ChrW(13)&Global.Microsoft.
VisualBasic.ChrW(10)&"SELECT
> tblCountryId, Country3LetterISOCode, CountryNameL1, Cou"& _
> "ntryNameL2, CurrencyName, CurrencySymbol, CreatedBy, CreatedOn,
> LastModifiedBy, "& _
> "LastModifiedOn, ts FROM tblCountries WHERE (tblCountryId =
> SCOPE_IDENTITY())"
> I think that what happens is that because the parameters @.CreatedBy and
> @.CreatedOn must be getting passed as null values (I don't show them in or
> affect them in my UI elements) by the Visual Studio program, they override
> the default values specified in the table definitions, so the net result
> is that when you look at the table after doing an insert with default
> Visual studio generated code, you end up not getting any values in the
> cells set with a default value in the table. I'm going to try to remove
> these default fields from the table adapter in the Visual studio code and
> see if that solves the problem.
> Thanks for the tip of looking at the insert statement, its what got me
> thinking on this possibility.
> Regards,
> Bob
>
>
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23HlOGf8DGHA.2088@.TK2MSFTNGP09.phx.gbl...
>|||Bob,
Just want to let you know, I recently made a post addressing the same
peculiarity.
And after soul searching and some thought, I did some testing and have
confirmed what does seem to make sense based on the way the
"disconnected" ado works.
When your dataset datatable has a schema that includes fields with
defaults and triggers (I had an UPDATE trigger), the cache will have
<NULL> for them upon the .fill method of the TableAdapter (I used a
datagridview too). So when you make your changes or add a new record,
the update method sends Nulls (not sure if it is Null or DBNull) back
to the datasource table and hence SQL Server thinks you've given it a
value (of it is Null at this point) and doesn't set a default value. I
can only assume that this Null is different than a standard Null that
SQL Server gets from the Enterprise Manager or Query Analyser.
Also, if there is a trigger on a field, SQL Server thinks that you've
added data to that field and in my case (I tested for "If NOT
UPDATE(myfield)") the trigger did'nt fire, again
suggesting that the Null sent back to the SQL Server from the
application is different than the Null in EM or QA.
VB6 ado may have not behaved in this "disconnected" way, at least when
it came to this situation.
Any thoughts?
Christopher|||cefrancke@.yahoo.com wrote in news:1137325516.855206.30800
@.g47g2000cwa.googlegroups.com:

> So when you make your changes or add a new record,
> the update method sends Nulls (not sure if it is Null or DBNull) back
> to the datasource table and hence SQL Server thinks you've given it a
> value (of it is Null at this point) and doesn't set a default value. I
> can only assume that this Null is different than a standard Null that
> SQL Server gets from the Enterprise Manager or Query Analyser.
> Also, if there is a trigger on a field, SQL Server thinks that you've
> added data to that field and in my case (I tested for "If NOT
> UPDATE(myfield)") the trigger did'nt fire, again
> suggesting that the Null sent back to the SQL Server from the
> application is different than the Null in EM or QA.
>
You are right in that this is what happens, but you are wrong about
nulls in QA, EM.
Test this from QA:
--create a table with a default column
create table testtab (id int primary key, col1 int default 10)
--insert one record
insert into testtab (id) values (1);
select * from testtab;
--you should now see 1, 10
--insert another record
insert into testtab values(2, null);
select * from testtab
--you should now see 2, null
null IS a value, and if you explicitly sends in NULL to a table which
has a default column, SQL Server sees that you have set a value for that
column and therefore doesn't assign the default value. It isn only if
you have not set a value for the column that the default will be
assigned.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********

Default values

How can I assign a field in a table a default date. I know that I can use
getdate(), but I don't want to store the time, only the date. Can this be
done in the table definition?
Thanks
JeffInstead of...
CREATE TABLE foo(dt SMALLDATETIME DEFAULT GETDATE())
...use...
CREATE TABLE foo(dt SMALLDATETIME DEFAULT CONVERT(CHAR(8), GETDATE(), 112))
Note that a time portion will still be stored, but it will be midnight...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:e$B9q6rMEHA.620@.TK2MSFTNGP10.phx.gbl...
> How can I assign a field in a table a default date. I know that I can use
> getdate(), but I don't want to store the time, only the date. Can this be
> done in the table definition?
> Thanks
> Jeff
>|||Try making your default something like
convert(datetime, convert(varchar(20), getdate(), 1))
Jeff Duncan
MCDBA, MCSE+I
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:e$B9q6rMEHA.620@.TK2MSFTNGP10.phx.gbl...
> How can I assign a field in a table a default date. I know that I can use
> getdate(), but I don't want to store the time, only the date. Can this be
> done in the table definition?
> Thanks
> Jeff
>|||This is covered in http://www.karaszi.com/sqlserver/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message news:e$B9q6rMEHA.620@.TK2MSFTNGP10.phx.gbl...
> How can I assign a field in a table a default date. I know that I can use
> getdate(), but I don't want to store the time, only the date. Can this be
> done in the table definition?
> Thanks
> Jeff
>

Default Value or Binding = (getdate())

Ok I have a script to generate a database, and newly added to the database is a date fild for a specfic table. I have the 'Default value or binding' set to (getdate()) how exactly would you add that to the script for then the table is initialy generated. Or is it soemthing I would need another script to do right after the table generation.

This is the script for the table in question:

CREATE TABLE [dbo].[cust_file] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[customer_id] [int] NULL ,
[filename] [varchar] (255) NULL ,
[filedata] [image] NULL ,
[contenttype] [varchar] (255) NULL ,
[length] [int] NULL,
[added_date] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Any help would be great,
Tim Meers
Wannabe developer.

You just add "DEFAULT GETDATE()" after the NULL or whatever...

I'm pretty sure that's the syntax, but if not, I'll post again in about 2 minutes with the right stuff.

Please mark this post as the answer if it suits your needs :)

Thanks,

|||

Wow, I was way off :)

Peace,

|||

Do you mean create a table with a date column that gets populated automatically?

CREATE TABLE [dbo].[Junk]([id] [int]IDENTITY(1,1)NOT NULL,[description] [varchar](50)NULL,[added_date]AS (getdate()))

By the way, in SQL Studio, you can right click a table, select "Script Table as... Create to..." and it will generate the Create Table script for you.

|||

You don't have to create constraint for default value. You 1st post is correct with the exception of 'getdate()'. You don't have to enclose it in quotes

CREATE TABLE [dbo].[cust_file]

(
[id] [int] IDENTITY (1, 1) NOT NULL ,
[customer_id] [int] NULL ,
[filename] [varchar] (255) NULL ,
[filedata] [image] NULL ,
[contenttype] [varchar] (255) NULL ,
[length] [int] NULL,
[added_date] [datetime] NULL DEFAULT GETDATE()
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

|||

SGWellens:
Do you mean create a table with a date column that gets populated automatically?
CREATE TABLE [dbo].[Junk](
[id] [int]IDENTITY(1,1)NOT NULL,
[description] [varchar](50)NULL,
[added_date]AS (getdate()))
By the way, in SQL Studio, you can right click a table, select "Script Table as... Create to..." and it will generate the Create Table script for you.

Don;t think that is what the poster wanted Smile . added_date will always return the current date & time Stick out tongue

|||

khtan:
You don't have to create constraint for default value. You 1st post is correct with the exception of 'getdate()'. You don't have to enclose it in quotes
CREATE TABLE [dbo].[cust_file]
(
[id] [int] IDENTITY (1, 1) NOT NULL ,
[customer_id] [int] NULL ,
[filename] [varchar] (255) NULL ,
[filedata] [image] NULL ,
[contenttype] [varchar] (255) NULL ,
[length] [int] NULL,
[added_date] [datetime] NULL DEFAULT GETDATE()
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Exactly what I needed, thank you very much for your assistance.

Tim Meers
Wannabe Developer

|||

I knew I had it right the first time... but when I "double checked" by doing "Generate Create Script" ... SQL gave me that crazy constraint crap :(

Glad someone got it.

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...
> > 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
>> >> >
>> >>
>> >>
>> >
>> >
>

Default value does not get written

I have a CreatedOn field , datetime, which has GetDate() as the default
value. When I create a new record in the table itself, the field gets
populated OK, but when I try to create a new record with a Microsoft
datagridview control, I notice that the datecreated value does not get
created. It looks as if the datagridview insert statements override the
default value and prevent the default defined in the table to get written.
Any ideas on how to overcome this? I<ve looked in the dataridview controls
properties and the dataset properties for this table and don't find anything
simple to let me ensure that on an insert my default value defined in the
table is the one saved.
It looks like I will have to do something in SQL server itself.
Any help would be appreciated.
BobThis is not an engine issue. I suggest you post this to an ADO.NET group whe
re such experts
hopefully has some suggestions. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bob" <bdufour@.sgiims.com> wrote in message news:e%23bHrSUmGHA.4268@.TK2MSFTNGP05.phx.gbl...

>I have a CreatedOn field , datetime, which has GetDate() as the default val
ue. When I create a new
>record in the table itself, the field gets populated OK, but when I try to
create a new record with
>a Microsoft datagridview control, I notice that the datecreated value does
not get created. It
>looks as if the datagridview insert statements override the default value a
nd prevent the default
>defined in the table to get written.
> Any ideas on how to overcome this? I<ve looked in the dataridview controls
properties and the
> dataset properties for this table and don't find anything simple to let me
ensure that on an
> insert my default value defined in the table is the one saved.
> It looks like I will have to do something in SQL server itself.
> Any help would be appreciated.
> Bob
>|||It would be easier for us to assist you if you were to provide the table
DDL.
I suspect that the application/dataadapter/ADO is taking the current value
of the datagrid cell (probably an zero or empty string) and using that in
the INSERT command.
Default values ONLY occur IF no value is INSERTed. If an empty string or
zero is INSERTed, then it is accepted.
If you are unable to control this behaviour in the application, you may have
to create a INSERT Trigger that changes the zero/empty strings into
getdate().
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Bob" <bdufour@.sgiims.com> wrote in message
news:e%23bHrSUmGHA.4268@.TK2MSFTNGP05.phx.gbl...
>I have a CreatedOn field , datetime, which has GetDate() as the default
>value. When I create a new record in the table itself, the field gets
>populated OK, but when I try to create a new record with a Microsoft
>datagridview control, I notice that the datecreated value does not get
>created. It looks as if the datagridview insert statements override the
>default value and prevent the default defined in the table to get written.
> Any ideas on how to overcome this? I<ve looked in the dataridview controls
> properties and the dataset properties for this table and don't find
> anything simple to let me ensure that on an insert my default value
> defined in the table is the one saved.
> It looks like I will have to do something in SQL server itself.
> Any help would be appreciated.
> Bob
>

Friday, February 24, 2012

Default values do not get created in SQL 2005 tables

Default values

Default Value or Binding = (getdate())

Sunday, February 19, 2012

Default value GetDate() on column

Default value GetDate() on column

Default value GetDate() on column

Default value does not get written

sql delete multiple tables

Blog Archive

About Me