dateadd function

Question

Post reply

dateadd function

aysegul

Right there with Babe

Points: 778
More actions
January 22, 2008 at 1:16 am

#185832

Hi All,

I need help about the syntax of the sql as below

When I try to Execute the tsql as below I get the error message

declare @IlkTarih datetime

set @IlkTarih='01.01.2008'

select dateadd(dd,1,convert(datetime,@IlkTarih,103))

Msg 242, Level 16, State 3, Line 5

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Kind regards

Viewing 8 posts - 1 through 7 (of 7 total)

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

SwePeso SSC-Dedicated Points: 39725 More actions · Answer 1

SwePeso

SSC-Dedicated

Points: 39725

January 22, 2008 at 1:19 am

#770364

I don't think points are valid date separator for style 103.

N 56°04'39.16"
E 12°55'05.25"

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 2

Chandra Sekhara Vyas Dhara

SSChampion

Points: 10207

January 22, 2008 at 1:30 am

#770366

I am able to execute without any problem. I tried on sql server 2005

See the following code

declare @a datetime

set @='01.01.2008'

select dateadd(dd,1,convert(datetime,@a,103))

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

Seems to be a problem with the language setting.

Easiest method to avoid it is to use ISO format for the stringdate.

Try '20080101' instead and see what happens.

Also, the convert(datetime, @variable, 103) is redundant, since @variable is already a datetime, the style 103 won't be applied. (and as mentioned earlier, style 103 is not dotted delimiters anyway)

/Kenneth

Piotr.Rodak SSCrazy Eights Points: 9296 More actions · Answer 4

Piotr.Rodak

SSCrazy Eights

Points: 9296

January 22, 2008 at 3:49 am

#770405

Yes I can do it as well, but I suppose the problem is in first assignment. The variable is declared as datetime but then a string is assigned to it without explicit style applied. What is the value of the variable before last operation?

...and your only reply is slàinte mhath

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

As someone said above, date format #103 is NOT valid for this style of date. Check Books Online under CONVERT... the style you are looking for is style # 104.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions