Weird GETDATE() problem when INSERTing

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Weird GETDATE() problem when INSERTing

Author

Topic

drongo
Starting Member

2 Posts

Posted - 2011-01-20 : 13:19:17

I have a table for invoices. One column is called InvoiceDate. The default value on that column is GETDATE()

Now, when I use an insert statement like this:

INSERT INTO Invoices (Col1, Col2) VALUES (Val1, Val1)

sql always inserts the date but with the time part zeroed off. Here are the latest ones:

2011-01-20 00:00:00.000
2011-01-20 00:00:00.000
2011-01-19 00:00:00.000
2011-01-18 00:00:00.000
2011-01-18 00:00:00.000

But when I use an insert statement which includes the InvoiceDate column, like this:

INSERT INTO Invoices (Col1, Col2, InvoiceDate) VALUES (Val1, Val2, GETDATE())

sql inserts the time part as you would expect:

2011-01-20 17:51:15.007
2011-01-20 16:29:11.753
2011-01-20 16:05:42.080
2011-01-08 14:13:10.473
2010-09-21 12:22:50.107

So what is going wrong? As I say the default value on the column is GETDATE().

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-20 : 13:39:29

Your default is wrong. Show us the code for it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

drongo
Starting Member

2 Posts

Posted - 2011-01-21 : 03:52:37

Thanks, I guessed that might be it. Here is the CREATE statement for the table (I've removed the irrelevant columnns):

CREATE TABLE [dbo].[tblInvoices](
[InvoiceID] [int] IDENTITY(1,1) NOT NULL,
[CustomerID] [int] NOT NULL CONSTRAINT [DF_tblInvoices_CustomerID] DEFAULT ((0)),
[InvoiceDate] [datetime] NOT NULL CONSTRAINT [DF_tblInvoices_InvoiceDate] DEFAULT (getdate()),
CONSTRAINT [PK_tblInvoices] PRIMARY KEY CLUSTERED
(
[InvoiceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-21 : 12:43:00

It's working fine for me:

CREATE TABLE [dbo].[tblInvoices](
[InvoiceID] [int] IDENTITY(1,1) NOT NULL,
[CustomerID] [int] NOT NULL CONSTRAINT [DF_tblInvoices_CustomerID] DEFAULT ((0)),
[InvoiceDate] [datetime] NOT NULL CONSTRAINT [DF_tblInvoices_InvoiceDate] DEFAULT (getdate()),
CONSTRAINT [PK_tblInvoices] PRIMARY KEY CLUSTERED
(
[InvoiceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

insert into tblinvoices (customerid)
values (1)

select * from tblinvoices

drop table tblinvoices

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources