February 1, 2018 at 12:42 am
Hi experts,
Why below statement giving the error and not considering as datetime.Please advise.
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
declare @snapshotdate datetime='24-03-2016 00:00:00'
select isdate('24-03-2016 00:00:00') -- This is giving as 0
Regards
Vasanthlal
February 1, 2018 at 1:29 am
It's defaulting to the American format, month/day/year, hence you're trying to convert the 3rd day of the 24th month, which doesn't exist.
Use unambiguous formats (preferred), or use CONVERT with a format code.declare @snapshotdate datetime='2016-03-24 00:00:00'
select isdate(@snapshotdate)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 1, 2018 at 2:41 am
Thank you 🙂
February 7, 2018 at 3:58 am
This was removed by the editor as SPAM
February 7, 2018 at 4:58 am
your date format is not correct. it should be month/date/year. your are trying to do with date/month/year which is not correct according to format.
February 8, 2018 at 5:06 pm
shwetakakran01 - Wednesday, February 7, 2018 4:58 AMyour date format is not correct. it should be month/date/year. your are trying to do with date/month/year which is not correct according to format.
Who's format? day-month-year is actually a very common format in Europe. The problem isn't the format per se. As Gail pointed out, the problem is the format in conjunction with the regional settings... That or use the universal YYYY-MM-DD HH:mm:SS or YYYYMMDD formats.
If the OP is stuck having to deal with the DD-MM-YYYY... format, the CONVERT or TRY_CONVERT functions can be an easy fix...
SELECT CONVERT(DATETIME, '24-03-2016 00:00:00', 103);
-- or
SELECT ISDATE(CONVERT(DATETIME, '24-03-2016 00:00:00', 103));
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply