conversion of char to datetime data

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)

conversion of char to datetime data

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-10-16 : 08:13:10

Hi,

When I execute my sp I get an error.
exec updCandExpDate @Candidate_id=710071,@Expiration_Date='11/29/2008'

The error:
Msg 242, Level 16, State 3, Procedure updCandExpDate, Line 9
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

I have the following sp:



ALTER procedure [dbo].[updCandExpDate]
@Expiration_Date varchar(10),
@candidate_id int
as
BEGIN
SET DATEFORMAT DMY;
update candidate 

set Expiration_Date = convert(datetime,@Expiration_Date,103)
where candidate_id = @candidate_id


END

Can someone please advise me?

Many thanks.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 08:15:09

m/d/y is type 101, not 103

Kristen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-16 : 08:15:36

Rule #1: Don't use character data type to store datetime values
Rule #2: Use ISO (yyyymmdd) format to pass datetime values manually

exec updCandExpDate @Candidate_id=710071,@Expiration_Date='20081129'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 08:19:17

Except that the OP is deliberately converting from varchar to datetime ... so its probably intentional!!

But for sure if it was possible to send the parameter as yyyymmdd that would be better.

Kristen

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-10-16 : 08:25:57

Thanks for the response.
If i send the parameter in the format yyyymmdd what data type would it be? Also char?

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-16 : 08:30:53

quote:
Originally posted by collie

Thanks for the response.
If i send the parameter in the format yyyymmdd what data type would it be? Also char?

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Always use proper DATETIME datatype to store dates

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 10:10:03

"If i send the parameter in the format yyyymmdd what data type would it be? Also char?"

You can just use DATETIME native format at that point:


ALTER procedure [dbo].[updCandExpDate]
@Expiration_Date datetime,
@candidate_id int
as
...
exec updCandExpDate ..., @Expiration_Date = '20081129'

mixing a String calling parameter with a Datetime Sproc parameter will work just fine. SQL Server will make an implicit conversion, and the yyyymmdd format will mean that there will be no ambiguity - whatever Locale your server is set to etc.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources