datetime datatype problem

Question

datetime datatype problem

Betty Sun

SSCrazy

Points: 2442
More actions
January 26, 2006 at 11:33 am

#91397

Hi all,
I have a table like this:
CREATE TABLE [Dependents] (
[DependentID] [int] IDENTITY (1, 1) NOT NULL ,
[txndatetime] [datetime] NULL CONSTRAINT [DF__Dependent__txnda__7D78A4E7] DEFAULT (getdate()),
[FName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MI] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SSN] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Sex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DependentSSN] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK__Dependents__7C8480AE] PRIMARY KEY CLUSTERED
(
  [DependentID]
&nbsp ON [PRIMARY] ,
CONSTRAINT [FK__Dependents__SSN__145C0A3F] FOREIGN KEY
(
  [SSN]
&nbsp REFERENCES [Employees] (
  [SSN]
&nbsp
) ON [PRIMARY]
GO
And the txndatetime data type is datetime, the default value is getdate()
and I have a procedure call add_dependent, when I
exec add_dependent 'Jack', '', 'Smith', '123-11-2222', 'M', '333-33-4444'
I gives me the error message like this:
Server: Msg 8114, Level 16, State 4, Procedure add_dependent, Line 0
Error converting data type varchar to datetime.
I tried
exec add_dependent getdate, 'Jack', '', 'Smith', '123-11-2222', 'M', '333-33-4444'
Server: Msg 8114, Level 16, State 4, Procedure add_dependent, Line 0
Error converting data type nvarchar to datetime.
I tried:
exec add_dependent cast(getdate as varchar), 'Jack', '', 'Smith', '123-11-2222', 'M', '333-33-4444'
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'getdate'.
Mu procedure definition is
PROCEDURE add_dependent
@txntime datetime,
@FName varchar(50),
@MI char(1),
@lname varchar(50),
@ssn varchar(11),
@sex char(1)=NULL,
@dpssn varchar(11)
I am so confused

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Hello Betty,

You have defined the column to have the default value of "getdate()" in the table structure. Then why are you again accepting the date as input in the stored procedure.

Either you alter the table by removing the default value of "getdate()" or otherwise modify the stored procedure to remove the "@txntime" as input value.

Hope you are clear now.

Thanks and have a nice day!!!

Lucky

Betty Sun SSCrazy Points: 2442 More actions · Answer 2

Wow, good catch, I thought it shouldn't matter.

The error messages never give me any clue. I delete the parameter in store procedure.

Thank you, thank you.

Betty

Ray M SSC-Insane Points: 21093 More actions · Answer 3

getdate is a function and requires the '()' to execute

select getdate () -- works

select getdate -- fails

also you cannot use getdate() as a value for an in parameter.

exec add_dependent getdate(), 'Jack', '', 'Smith', '123-11-2222', 'M', '333-33-4444'

will fail

but as lucky says, you really don't need that parameter in your list. If you want it there supply a default value and test for that value.

I prefer to call procedures well formatted.

exec add_dependent

@txntime = '01/01/1900',

@FName = 'Fred',

@MI = 'B',

@lname 'Flinstone',

@ssn ='111-22-3333',

@sex = 'S',

@dpssn = 'dsfasd'

SQLBill SSC Guru Points: 51440 More actions · Answer 4

Removing the @txntime is the best option UNLESS you may have to enter that value.

Other options:

Change the order of the parameters when you create the procedure:

PROCEDURE add_dependent

@FName varchar(50),

@MI char(1),

@lname varchar(50),

@ssn varchar(11),

@sex char(1)=NULL,

@dpssn varchar(11),

@txntime datetime

or run your command using the parameters...

exec add_dependent @FName = 'Jack', '', @lname = 'Smith', @ssn = '123-11-2222', @sex = 'M', @dpssn = '333-33-4444'

Best part of doing it the latter way is that there is no confusion as to which value is which. In your example, is 123-11-2222 the @ssn or @dpssn?? BTW-another thing I noticed is you combine upper and lower case inconsistently (FName and lname). This could be a problem if your database collation is set for CASE SENSITIVE.

-SQLBill

Betty Sun SSCrazy Points: 2442 More actions · Answer 5

SQLBill,

Good point, I will follow the good practice in programming.

Betty

bobsterboy SSCommitted Points: 1549 More actions · Answer 6

I don't think you can use functions as defaults, either.

Vee Hall of Fame Points: 3028 More actions · Answer 7

I don't think you can use functions as defaults, either.

Vee Hall of Fame Points: 3028 More actions · Answer 8

I don't think you can use functions as defaults, either.

Why , then how come getdate() function working for default value.

bobsterboy SSCommitted Points: 1549 More actions · Answer 9

you're right. It's user defined functions that aren't allowed to be used as defaults. My bad.