default date

in DateTime column called ExpireDate, I have default value of (1/1/2020)
yet when data entry is made (without ExpireDate value) the value is always
set at 1/1/1900
why is default not entered as 1/1/2020 ?
DEFAULTs only work when you don't provide a value for the column at all, or
use the keyword DEFAULT. What it looks like is that you provide the value 0
for the column, and 0 as a datetime is interpreted by SQL Server as
1/1/1900. See the following example:
CREATE TABLE TJS(Expire_Date DATETIME DEFAULT '20200101')
INSERT INTO TJS (Expire_Date) VALUES (0)
INSERT INTO TJS (Expire_Date) VALUES (DEFAULT)
SELECT Expire_Date FROM TJS
Jacco Schalkwijk
SQL Server MVP
"TJS" <nospam@.here.com> wrote in message
news:1132gu53p4kt9bd@.corp.supernews.com...
> in DateTime column called ExpireDate, I have default value of (1/1/2020)
> yet when data entry is made (without ExpireDate value) the value is always
> set at 1/1/1900
> why is default not entered as 1/1/2020 ?
>
>
|||your example works, but I am trying to use a stored procedure
I have this in the stored procedure:
@.ExpireDate datetime = DEFAULT
The column default value is set as (1/1/2020)
but it still enters 1/1/1900
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:uIdTaWhJFHA.576@.TK2MSFTNGP15.phx.gbl...
> DEFAULTs only work when you don't provide a value for the column at all,
> or use the keyword DEFAULT. What it looks like is that you provide the
> value 0 for the column, and 0 as a datetime is interpreted by SQL Server
> as 1/1/1900. See the following example:
> CREATE TABLE TJS(Expire_Date DATETIME DEFAULT '20200101')
> INSERT INTO TJS (Expire_Date) VALUES (0)
> INSERT INTO TJS (Expire_Date) VALUES (DEFAULT)
> SELECT Expire_Date FROM TJS
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "TJS" <nospam@.here.com> wrote in message
> news:1132gu53p4kt9bd@.corp.supernews.com...
>
|||hi,
TJS wrote:
> your example works, but I am trying to use a stored procedure
> I have this in the stored procedure:
> @.ExpireDate datetime = DEFAULT
> The column default value is set as (1/1/2020)
> but it still enters 1/1/1900
>
do you mean your procedure's code is
DECLARE @.ExpireDate datetime
SELECT @.ExpireDate = DEFAULT
INSERT INTO #test VALUES ( 1 , @.ExpireDate )
or
DECLARE @.ExpireDate datetime
SELECT @.ExpireDate = '20200101'
INSERT INTO #test VALUES ( 2 , @.ExpireDate )
?
the first code will actually raise an exception (Incorrect syntax near the
keyword 'DEFAULT'.) and non data will be entered...
can you please expand?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Alter PROCEDURE dbo.AddUser
(
@.Name nvarchar(50),
@.Email nvarchar(100),
@.Password nvarchar(50),
@.ExpireDate datetime = DEFAULT,
@.EnableNewsLetter bit,
@.UserID int OUTPUT
)
AS
INSERT INTO _Users
(
Name,
Email,
Password,
ExpireDate,
EnableNewsletter
)
VALUES
(
@.Name,
@.Email,
@.Password,
@.ExpireDate,
@.EnableNewsLetter
)
SELECT
@.UserID = @.@.Identity
|||hi TJS,
TJS wrote:
> Alter PROCEDURE dbo.AddUser
> (
> @.Name nvarchar(50),
> @.Email nvarchar(100),
> @.Password nvarchar(50),
> @.ExpireDate datetime = DEFAULT,
> @.EnableNewsLetter bit,
> @.UserID int OUTPUT
>.....
you can not use the DEFAULT keyword that way as you have to provide an
explicit default and not the "DEFAULT" keyword if you want it to be used for
not provided paramenter... that's to say you have perhaps to set it as
@.ExpireDate datetime = 'some date',
if you check your code, @.ExpireDate will always be NULL if not explicit
value has been specified for that parameter...
your code is like
SET NOCOUNT ON
GO
CREATE TABLE dbo._Users (
UserID int IDENTITY
, Name nvarchar (10) --(50)
, Email nvarchar (20) --(100)
, Password varchar(10) --(50)
, ExpireDate datetime DEFAULT '20050101'
, EnableNewsletter bit DEFAULT 0
)
GO
CREATE PROC dbo.AddUser (
@.Name nvarchar(50)
, @.Email nvarchar(100)
, @.Password nvarchar(50)
, @.ExpireDate datetime = DEFAULT -- this value will never be used and the
underlaying
-- column default can not be used
, @.EnableNewsLetter bit
, @.UserID int OUTPUT
)
AS
-- SELECT @.ExpireDate always returns NULL if no explicit value is passed
INSERT INTO dbo._Users
(
Name
, Email
, Password
, ExpireDate
, EnableNewsletter
)
VALUES
(
@.Name
, @.Email
, @.Password
, @.ExpireDate
, @.EnableNewsLetter
)
SELECT @.UserID = SCOPE_IDENTITY()
GO
DECLARE @.UserId int
EXEC dbo.AddUser @.Name = 'Andrea'
, @.Email = 'andrea@.andrea.com'
, @.Password = 'aerdna'
-- , @.ExpireDate -- param not provided
, @.EnableNewsLetter = 1
, @.UserId = @.UserId OUTPUT
EXEC dbo.AddUser @.Name = 'Andrea'
, @.Email = 'andrea@.andrea.com'
, @.Password = 'aerdna'
, @.ExpireDate = NULL -- param exlicitely NULL
, @.EnableNewsLetter = 1
, @.UserId = @.UserId OUTPUT
EXEC dbo.AddUser @.Name = 'Andrea'
, @.Email = 'andrea@.andrea.com'
, @.Password = 'aerdna'
, @.ExpireDate = '20050315' -- param provided
, @.EnableNewsLetter = 1
, @.UserId = @.UserId OUTPUT
SELECT *
FROM dbo._Users
-- WHERE UserID = @.UserId
GO
DROP PROC dbo.AddUser
DROP TABLE dbo._Users
--<--
UserID Name Email Password ExpireDate
EnableNewsletter
-- -- -- -- --
-- --
1 Andrea andrea@.andrea.com aerdna NULL
1 -- no value specified
2 Andrea andrea@.andrea.com aerdna NULL
1 -- explicit NULL specified
3 Andrea andrea@.andrea.com aerdna 2005-03-15
00:00:00.000 1
but modifyng the daclaration of the sp's parameters, providing an explicit
value for that parameter like
CREATE PROC dbo.AddUser (
@.Name nvarchar(50)
, @.Email nvarchar(100)
, @.Password nvarchar(50)
, @.ExpireDate datetime = '20050101'
, @.EnableNewsLetter bit
, @.UserID int OUTPUT
)
AS
....
you will get a different result as
--<--
UserID Name Email Password ExpireDate
EnableNewsletter
-- -- -- -- --
-- --
1 Andrea andrea@.andrea.com aerdna 2005-01-01
00:00:00.000 1
2 Andrea andrea@.andrea.com aerdna NULL
1
3 Andrea andrea@.andrea.com aerdna 2005-03-15
00:00:00.000 1
you can perhaps check your parameters like
IF ISNULL ( @.ExpireDate ) BEGIN
-- set it to whatever you want
END
or execute 2 different INSERT statements depending on the IF condition, ie:
do not provide the [ExpireDate] column if you want it to default to your
CREATE TABLE column default like
IF ISNULL ( @.ExpireDate ) BEGIN
INSERT INTO dbo._Users ( Name , Email , Password , EnableNewsletter )
VALUES ...
ELSE
INSERT INTO dbo._Users ( Name , Email , Password , ExpireDate ,
EnableNewsletter ) VALUES ...
but I'd better check for ISNULL and set it accordingly to your needs, as all
you other parameters should be checked as well
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||more...
you can even query the INFORMATION_SCHEMA.COLUMNS ANSI view for columns
information like nullability and default to perform your own check and
eventual default settings...
SET NOCOUNT ON
CREATE TABLE dbo.Test (
ID int NOT NULL ,
dt datetime DEFAULT getdate()
)
GO
SELECT c.COLUMN_DEFAULT , c.IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = 'dbo'
AND c.TABLE_NAME = 'Test'
-- AND c.COLUMN_NAME = 'dt'
GO
DROP TABLE dbo.Test
--<--
COLUMN_DEFAULT IS_NULLABLE
-- --
NULL No
(getdate()) YES
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply