DateAdd poblem

Transact-SQL
1

Hi,

I try to add a number of days to the current date. In general it is not a problem, but I have not direct access to SQL Server and the request will be transferred via a middleware. However, I have the following statement:

Select DATEADD(day,%1,CONVERT(DATETIME, getdate(), 103))

%1 is a variable for the middleware. The problem is, that all values are transferred with quotes.

If I want to add days I set %1 in the middleware and in the sql statement it will be '10'. As consequence I receive the following error:

[[Microsoft][ODBC SQL Server Driver][SQL Server]Argument data type varchar is invalid for argument 2 of dateadd function. - Select DATEADD(day,'10',CONVERT(DATETIME, getdate(), 103))]

I there a way to remove the quotes in the SQL statement? I tried this:

replace(%1,'''','')

but it didn't work.I hope you have some ideas.

Best regards,

Lara

2

Would this work?

Select DATEADD(day,CONVERT(int, %1),CONVERT(DATETIME, getdate(), 103))

???
By the by, it would be much better not to use Text Strings in this formula - .e. converting GetDate() to 'yyyy-mm-dd' with format code 103. If you stick to Artihmetic it performs better, and 'yyyy-mm-dd' is treated as ambiguous by SQL Server (you need to use "yyyymmdd" [no punctuation] for an UNambiguous date. If the Locale of your server, OR the currently-connected-user, were to change, SQL would raise an error. For example this will give you an error, although it is fine in US-English Locale

SET LANGUAGE 'French'
SELECT CONVERT(DATETIME, '2015-12-31')

This will give you an arithmetic-only solution - although its a bit obtuse to read!

SELECT DATEADD(Day, DATEDIFF(Day, 0, GetDate()) + @DaysToAdd, 0)

which, if my CAST workaround above works for you, you could code as:

SELECT DATEADD(Day, DATEDIFF(Day, 0, GetDate()) + CONVERT(int, %1), 0)

on recent version of SQL you can use DATE instead of DATETIME when you want whole-days

SELECT DATEADD(Day, @DaysToAdd, CONVERT(Date, GetDate()))
3

Hi Kristen,

man thanks for your quick help and additional information. Your hint with CONVERT(int, %1)

works. I'm happe.:smile:
Have a good day..

Lara