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?
"The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value."
Any ideas?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>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).
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).
Please disregard my comment. I had failed to refresh, and found that Jim had already addressed everything I had to say :)
< been there, done that, have the T-shirts ... >
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. :)
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).
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).
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))