Avatar of hrolsons
hrolsons
Flag for United States of America asked on

Date functions in SQL

I'm using DATEADD(d,10,myVal) function, but sometimes myVal is not in the format expected and I get:

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

Any ideas?
Microsoft SQL Server

Avatar of undefined
Last Comment
Anthony Perkins

8/22/2022 - Mon
Jim Horn

You can only do DATEADD() stuff on a date or datetime value, not on a nvarchar.

To pull this off and store the value as nvarchar, you're going to have to convert the nvarchar to a date, then do the DATEADD(), then convert the result back to nvarchar.

Declare @dt nvarchar(10) = '01-01-2011'
SELECT CAST(DATEADD(d, 10, CAST(@dt as DATE)) as nvarchar(10))
hrolsons

ASKER
I see what you're saying but having trouble implementing it into my query.  I'm wondering if storing my data as datetime in the first place might be a better option?  If that is the case, I want to make sure my data is in the correct format before I convert.
ASKER CERTIFIED SOLUTION
Jim Horn

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Patrick Matthews

>>I'm wondering if storing my data as datetime in the first place might be a better option?

Yes, absolutely!  Using the right data types is one of the crucial things to get right in the design phase.

>>If that is the case, I want to make sure my data is in the correct format before I convert.

You can use the ISDATE() function to test whether an [n][var]char value is convertible to a date (returns 1) or not (returns 0).
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Patrick Matthews

Please disregard my comment.  I had failed to refresh, and found that Jim had already addressed everything I had to say :)
Jim Horn

<  been there, done that, have the T-shirts ... >
hrolsons

ASKER
Strange, I wonder why MS Access can work with "Apr-20-12" but SQL can't.  I'm not complaining but...wait I guess I am.  :)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Anthony Perkins

I wonder why MS Access can work with "Apr-20-12" but SQL can't.
That would be because most SQL Server developers use the correct data type, so it is a non-issue.   :)

Incidentally,  Apr 20 12 is acceptable (although I would never consider using that format).