Date functions in SQL

hrolsons
hrolsons used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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))

Author

Commented:
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.
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
>I'm wondering if storing my data as datetime in the first place might be a better option?
It always is.  

The only exception I'm aware of is if the source of your data is somewhere else, to first store it as a varchar just to make sure the values are imported correctly.  Then you can do an ISDATE() to make sure they are dates, and insert them into a datetime column, and deal with anything that does not translate to a date.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Top Expert 2010

Commented:
>>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).
Top Expert 2010

Commented:
Please disregard my comment.  I had failed to refresh, and found that Jim had already addressed everything I had to say :)
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

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

Author

Commented:
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.  :)
Top Expert 2012

Commented:
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).

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial