problem with dateadd sql 6.5 1

[bluecjh]

SELECT @AppliedForTargetDate = DateAdd(Dd,14,@v_contactDate)

the above returns strange results, i was expecting
this to return a date 14 days later than @v_contactDate.
but the results are can be all over the place.

@v_contactdate is datetime type.

can anyone explain... thanks

 

[NoCoolHandle]

for the heck of it, run

select dateadd(dd,14,getdate())

a few times and see what you get.. I am suspicious that it is the contents of @v_contactDate is all over the place.

As a bad version of debugging, quite often (especially if I am seeing results that are not expected, I do a "print" of the variables that could cause problems. This usually allows me to pinpoint logic errors.

You could try changing to your code/proc to
SELECT @AppliedForTargetDate = DateAdd(Dd,14,@v_contactDate)
print convert(varchar(10), @appiledfortargetdate ,1) + ' ' + convert(varchar(10),@v_contactDate,1)

Good luck

Rob

 

[bluecjh]

Hi Rob,
it's not @v_contactDate that is unpredictable rather
the results e.g. when @v_contactDate = 5/11/02
i expected 19/11/02, i will add your suggestions to my list
of checks, I assume that is @v_contactDate that is the
problem as when I replace it with an actual date
the results are predictable

 

[SQLBill]

Give this a test:

SET DATEFORMAT mdy
GO
SELECT @AppliedForTargetDate = DateAdd(Dd,14,@v_contactDate)

Let us know if the results are still 'all over the place'.

(I have a feeling your query is being interpreted as DD/MM/YY)

-SQLBill

 

[bluecjh]

I think you must have it I am supplying
english date formats e.g.
'9 Oct 2003'
should I use:
SET DATEFORMAT dmy ?
I will try tomorrow, thanks Bill

 

[pascalsql]

Use ISO format: yyyymmdd
it's unambiguous and it sorts nicely.

BTW it should be the ONLY way to talk about dates, even in everyday language.

As a test date, I often use Christmas : 25-12-2003 ... or 12/25/03 according on where in the world you happen to live.

 

[SQLBill]

bluecjh,

Yes, I had it backwards...

SET dmy


also, pascalsql is correct...using yyyy-mm-dd is very unambiguous, SQL Server understands it completely (in my experience) and it's the only way I do dates.

-SQLBill

 

[bluecjh]

Sorted,
the problem lay elsewhere in my code but I have
learnt some things... thanks all...