Default CONVERT(INT, GETDATE()) gives 0 (zero) problem
-
I need a default value in a table for the integer part of Getdate.
E.g. Adding a new Integer Field "EnterDay" (not null) with a default (today should show 38390).
This works fine for a normal sql statement: CONVERT(INT, GETDATE())
But when I use it as Default then it always results as 0 (zero) for a new record.
Strange is that when adding this field it gives the correct day-number for existing records, only a new record gives a 0.
Does anyone know why?
And what is a correct statement for the Default giving 38390 for today (feb 8 2005)?
-
First, let me say, that the INT value of today is *not* 38390! Since we are now past 12:00:00 some methods round up which isn't correct. So, the correct value is 38389.
Does it need to be an INT column?
If not, you can have a DATETIME column with a DEFAULT like DATEADD(d,DATEDIFF(d,0,getdate()),0). That can be very easily converted to INT.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank, thank you for your answer.
You asked:
Does it need to be an INT column?
The answer is Yes, because it is an old application that has to use an former DB structure. The exact date is not my concern, as it is an "approximately" value.
My concern is that I get a 0 (zero) in a new record (and an integer in existing records when I add the field, with the dafault and not null).
-
Sorry, it is not an Integer field, it is a numeric (precision 10, scale 0) field.
But Convert gives still an 0 (zero)
-
Are you trying this from within EM???
This works fine for me with QA:
CREATE TABLE [dbo].[Table1] (
[c1] [int] NOT NULL ,
[c2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD
CONSTRAINT [DF_Table1_c1] DEFAULT (convert(int,getdate())) FOR [c1]
GO
INSERT INTO table1 (c2) VALUES('test')
SELECT * FROM Table1
c1 c2
----------- ----------
38390 test
(1 row(s) affected)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
...oops, forgot to mention. It works also in EM. You only need to refresh the view.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank,
Du bist WUNDERBAR.
...oops, ...
That was my problem.
I tried the whole thing in EM,
and after a refresh it shows the correct value, no zeros any more.
Btw, I'll use your "correct" date, it is better anyhow.
Another, thanks!
-
Glad I could help.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply