May 13, 2016 at 8:45 am
Hi
I've discovered that a some SQL code was crashing with this error :-
Msg 242, Level 16, State 3, Line 8
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
At first I thought it was a bug in the code then after spending a few hours on this (and nearly giving up), I discovered that the error wasn't due to a bug in the code, as the same code worked fine when running directly from a SQL Management Studio Express on my PC.
So this code works fine when I run from a PC but does not work when I run the same code on any of my servers via a Remote Desktop Session.
I've checked the Regional Setting on my PC and the servers and both are set up the same.
Please can anyone advise how to fix this.
This is the code that won't run when running directly from the server via a Remote Desktop Session and then running from MS SQL Management Studio:-
declare @MINUTES_WITHIN int
declare @latestdate datetime
set @MINUTES_WITHIN=60
set @latestdate = dateadd(mi, @MINUTES_WITHIN, getdate())
set @latestdate = replace(str(datepart(dd,@latestdate),2),' ','0') + '/'
+ replace(str(datepart(mm, @latestdate),2),' ','0')+ '/'
+ str(datepart(yy,@latestdate),4)
Kind Regards
Matthew
May 13, 2016 at 8:51 am
first you are being a bad boy, changing dates to strings and back to dates.
if you simply select your code, instead of assigning it, you will see you are generating a string value of "13/05/2016"
if your datetime format is MDY and not DMY, that's an out of date value.
declare @MINUTES_WITHIN int
declare @latestdate datetime
set @MINUTES_WITHIN=60
set @latestdate = dateadd(mi, @MINUTES_WITHIN, getdate())
select replace(str(datepart(dd,@latestdate),2),' ','0') + '/'
+ replace(str(datepart(mm, @latestdate),2),' ','0')+ '/'
+ str(datepart(yy,@latestdate),4)
if you want to strip off time, you could shortcut it by converting to date
declare @MINUTES_WITHIN int
declare @latestdate datetime
set @MINUTES_WITHIN=60
set @latestdate = CONVERT(date,dateadd(mi, @MINUTES_WITHIN, getdate()))
Lowell
May 13, 2016 at 9:10 am
Hi Lowell
I wasn't the one who wrote this code -it was developers who wrote code years ago but I'm not going to defend them :-). I found this code from quite a large Stored Procedure to be causing this error when connected in remotely - I just have trouble running it from SQL Studio Express when remote desktop'd into the Servers (running Server 2005 or 2008). But when I run it SQL Studio Express from my PC (Windows 7) it works absolutely fine. It might not be an issue at all but I would like to prove it so I can rule it out being any concern.
My biggest concern is that the servers will not be running this kind of code properly. The other problem I have is that the server is over 300 km away so I need to prove if the server can handle this kind of code or not. The servers (newly upgraded from SQL 2005 to SQL 2008) are also experiencing slowness (plus users pcs now sometimes freeze up) and I'm thinking that this might be related to the issues. This is why I need to rule it out.
Please can you help some more regarding proving whether server will have an issue running this code. I'm not a developer but the Stored Procedure is typically called from an external API appointment kiosk machine connected locally to a doctor's server network - just for extra information.
Kind Regards
Matthew
May 13, 2016 at 9:23 am
it's not the server, it's your individual connection that is the issue.
something related to Remote desktop must be setting your regional settings, which in turn affects your specific session setting
run this command, and you'll see what your current connection thinks is the dateformat.
DBCC USEROPTIONS
you can modify your dateformat with an example like this; explicitly setting it would resolve the issue, but bulletproofing it with strict date or datetime datatypes through all the code would be my first line of defense to address this.
--the simple fix
SET DATEFORMAT DMY
declare @MINUTES_WITHIN int
declare @latestdate datetime
set @MINUTES_WITHIN=60
set @latestdate = dateadd(mi, @MINUTES_WITHIN, getdate())
set @latestdate = replace(str(datepart(dd,@latestdate),2),' ','0') + '/'
+ replace(str(datepart(mm, @latestdate),2),' ','0')+ '/'
+ str(datepart(yy,@latestdate),4)
Lowell
May 13, 2016 at 9:38 am
I'm not really in a position to change code at my company but will definitely consider, thanks for all your input so far also.
Interestingly when I run DBCC USEROPTION on my PC it outputs this
textsize 2147483647
language British
dateformat dmy
datefirst 1
lock_timeout -1
quoted_identifier SET
arithabort SET
ansi_null_dflt_on SET
ansi_warnings SET
ansi_padding SET
ansi_nulls SET
concat_null_yields_null SET
isolation level read uncommitted
but when remote desktopped in, it shows this :-
textsize 2147483647
language us_english
dateformat mdy
datefirst 7
lock_timeout -1
quoted_identifier SET
arithabort SET
ansi_null_dflt_on SET
ansi_warnings SET
ansi_padding SET
ansi_nulls SET
concat_null_yields_null SET
isolation level read committed
My Servers are run in Britain so at least its conforting to know that the server must be set to the British format.
It would good if I could change the settings in MS Studio Manager but not essential 🙂
Kind Regards
Matthew
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply