September 20, 2010 at 2:19 am
I have a column thats of type DateTime with a default value set as GetDate()
When rows are inserted, the datetime column value is always null, does anyone know why this should happen ?
September 20, 2010 at 2:24 am
it's they way a default constraint works...i think you are misunderstanding it.
a default constraint DOES NOT replace nulls with the default value...it puts the default value in if the column IS NOT REFERENCED in the insert statement
so if i have a table with name/createdate,
INSERT INTO ThatTable('bob') will use the default value; INSERT INTO ThatTable(;bob',NULL) puts a null value in the default column, because the INSERT explicitly said what the value should be.
so to get the desired effect, you need to either change hte insert so it doesn't reference the column, or add a trigger that updates the column when it is null;
hope that helps!
Lowell
September 20, 2010 at 4:03 am
I dont reference the column anywhere in my insert statement, im expecting the database to insert the current date, but it doesnt
September 20, 2010 at 4:09 am
Post the table definition and the insert statement that you are using.
Any trigger on the table?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2010 at 7:50 am
Hi
if Database wants to insert the current date, you have to create trigger on that table.
for ex:
-- sample code to generate time stamp for each row inserted
-- create sample table.
create table test
(
empno int,
ename varchar(10),
time_stamp datetime
)
-- create db trigger
create trigger ts on test after insert
as
begin
update test set time_stamp = getdate() where time_stamp is null
end
--insert sample records
insert into test (empno,ename) values (1,'aa')
insert into test (empno,ename) values (2,'bb')
select * from test
Thanks
Siva Kumar J.
September 20, 2010 at 10:22 am
sivaj2k (9/20/2010)
Hiif Database wants to insert the current date, you have to create trigger on that table.
The trigger is one way to handle it, but not the best choice. Defaulting is. Is the problem that when you apply the default, it's not backfilling? Otherwise, as mentioned, we'll need the table DDL and the procedure you're using that isn't giving you what you expect.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 20, 2010 at 11:26 am
sivaj2k (9/20/2010)
Hiif Database wants to insert the current date, you have to create trigger on that table.
That's true for updates and updated date, but not for inserts.
See revised example:
create table test
(
empno int,
ename varchar(10),
time_stamp datetime default getdate()
)
--insert sample records
insert into test (empno,ename) values (1,'aa')
insert into test (empno,ename) values (2,'bb')
select * from test
p.s. your trigger would update all the not null columns, not just ones inserted by the insert statement that fired the trigger. You should reference the inserted table in the trigger to only affect rows just inserted.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2010 at 1:07 pm
set the date column to not null, then it will use the default if you don't supply any data for that column
September 20, 2010 at 1:13 pm
josh_boudinot (9/20/2010)
set the date column to not null, then it will use the default if you don't supply any data for that column
Defaults will be used regardless of whether the column is nullable or not, providing that the column is not explicitly set in the insert statement.
CREATE TABLE #test (
id INT,
date1 DATETIME NULL DEFAULT GETDATE(),
date2 DATETIME NOT NULL DEFAULT GETDATE()
)
go
INSERT INTO #test (ID) VALUES (1) -- both date columns get default
INSERT INTO #test (ID, date1) VALUES (2, NULL) -- 1st date column gets null (explicitly set) second gets default
INSERT INTO #test (ID, date2) VALUES (3, NULL) -- error. Explicitly setting a not null column to null
go
SELECT * FROM #test
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2010 at 1:55 pm
Gail is correct, the column NULL setting doesnt' change defaults.
If you don't reference the column in your insert statement, the default should apply. For example:
CREATE TABLE [dbo].[MyFirstTable](
[MyID] [int] NULL,
[MyChar] [varchar](50) NULL,
[MyDate] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MyFirstTable] ADD CONSTRAINT [DF_MyFirstTable_MyDate] DEFAULT (getdate()) FOR [MyDate]
GO
INSERT MyFirstTable SELECT 1, 'A', '1/1/2010'
INSERT MyFirstTable( MyID, MyChar) SELECT 2, 'b'
go
SELECT * FROM MyfirstTable
If you notice, in the first insert, I include all 3 values. In the second, I ignore the MyDate column. The default is inserted. If I do this, I get a null
INSERT MyFirstTable SELECT 3, 'c', null
September 21, 2010 at 5:54 am
jboudinot (9/20/2010)
set the date column to not null, then it will use the default if you don't supply any data for that column
I've never seen that work.
Every time I've set a default column to NOT NULL, I'm forced to specify it in all INSERT statements and override it because the INSERT statement will fail if any NOT NULL column is not included at execution time, which rather defeats the purpose of the DEFAULT constraint.
September 21, 2010 at 6:14 am
Brandie Tarvin (9/21/2010)
Every time I've set a default column to NOT NULL, I'm forced to specify it in all INSERT statements and override it because the INSERT statement will fail if any NOT NULL column is not included at execution time, which rather defeats the purpose of the DEFAULT constraint.
?? See my example 2 posts up.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 21, 2010 at 7:11 am
I know that Gail and Steve have done it correctly. I'm just amazed at the number of, ummmm.... shall we say, "myths" that have come up on this one. Wow!
--Jeff Moden
Change is inevitable... Change for the better is not.
September 21, 2010 at 7:44 am
I actually have had errors in the past on this. Even posted a request for help on this forum about it at some point, but no one was able to help at the time.
I've also upgraded since then, so maybe it was an RTM issue.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply