I set up razuna with a MSSQL database. Since a couple of days I cannot login in razuna anymore.
I get “Error converting data type varchar to datetime”:
Obviously he cannot convert this part:
(u.user_expiry_date is null OR u.user_expiry_date >= '2015-12-17')
Current language is English where the dateformat is mdy
How can I resolve this issue? Any ideas?
Hi Linh ,
Follow the error description as showing by the image , Its seem to be this account used to login to Razuna was set expiration date and could not login to system anymore.
May I know are you able to login with another account or what kind of your user (administrator or user) ?
Did you ever set the expiration date for user ?
Thanks.
Hi Huy,
I tried several accounts and all of them doesn’t work.
There are no expiration dates:
If I set the dateformat like SET DATEFORMAT ymd then it works. Without it will fail.
Thanks
Hi Linh ,
If you able to login to admin back-end of this tenant , please perform reset cache of this tenant. Then try to login again to see how or after you set the dateformat and it works after that you can go to Maintenance to Flush database cache and flush template cache.
After all , try to login again.
Hope this helps.
Thanks
Hi Huy,
I cannot login to the admin nor to the user interface. 
I receive the same error on both ends.
I can set the dateformat temporarly in my SQL Management Studio session. Then I can execute the SQL Statements like SELECT * FROM users WHERE user_expiration_date < "2015-12-17"
I am not able to set the dateformat for razuna connection.
Any other idea how to resolve this?
The real question is what has changed on your end? You state that "I set up
razuna with a MSSQL database. Since a couple of days I cannot login in
razuna anymore.”
In short, you were able to sign in and all was working and then “all of the
sudden” it doesn’t work anymore? Seems to me more a MSSQL issue than a
Razuna one.
It is all
about dateformat. I set the database language to English-US. Due to that the
default dateformat is ydm.
If I
execute a sql with something like SELECT * FROM users WHERE user_expiration_date < '2015-12-01' it will be interpreted as 12th January
2015. It works as long as we reach '2015-12-13' where I receives the
conversion error as stated before. The only thing what changed was the time…
As I said
before, I can set the dateformat temporary for a session but not permanent. The
default dateformat is connected with the database language.
Razuna
should set the default date format for his session before it executes
statements with dates.
Yeah this is not going to make it into Razuna 1.8 as there is a freeze now
as we are short before release. Thanks for the update.
How can I access my data now? Is there a way to remove the expiry-date clause from the sql statement? Anything I can do as a workaround?
Hi Mr. Linh ,
You can access your data by set the dateformat temporary for a session as your description before. I think if you going to remove the expiry-date clause from the sql statement , that is a not good idea.
I cannot set it for the razuna session, since razuna creates his own session/connection.
This looks to be a MSSQL issue as the German version wants dates in another format. I suggest looking into the MSSQL settings to see if there is a way to have it set to US format for the Razuna database only. That is your best option.
(Why MSSQL server even changes dates when you run the server on a German operating system is beyond me in the first place)