This week I was working on a migration script where I needed to run multiple sql queries. Since I was going to use this only once, I decided to use SqlHelper classes to connect and run sql to MsSql2012. Part of this included inserting and updating a date into the database. On my local computer this was working fine however, when placed on the server, I kept on getting the below error:
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.
A sample of the Sql I was trying to use was:
UPDATE Table SET StartDate = '26-Mar-15 2:53:42 PM' WHERE ID = 15
I googled this error and amongst the top suggestions/solutions I found:
- sql query is passing the wrong date format so it goes out of range
- you should not pass DateTime values as sting in your SQL statement. Instead use parameters such as (
cmd.Parameters.AddWithValue("@mvendid", mVendid); )
None of the 2 above worked. I tried all sorts of formats (“dd/mm/yyyy hh:mm:ss”, “mm/dd/yyyy hh:mm:ss”, “yyyy/mm/dd hh:mm:ss”, etc etc). I tried also declaring a new date in the sql and saving it, which still gave same error. I was about to give up when I though what about the computer’s date?
We checked my computer’s date format from “Control Panel -> Region and Language -> Formats” and noticed that the short date was completely different. Since I was desperate, we tried changing the server’s local date to match my date. Just like a miracle it worked fine.
Hope this helped … as I was going mental for a while there!!!
Using “overflow:hidden”, as we all should know by now, sets the overflow of the h1 (or whatever element in use) hidden, making all text outside the element hidden too. Meanwhile, “white-space: nowrap” is used to avoid having the text breaking over different lines. We want out title to fit in a line. Finally, “text-overflow: ellipsis” is telling the browser that IF there is an overflow of text, add “…” at the end of the text before the rest is hidden.
Hope this is as helpful to you as it was was for!
Lets say I would like to query a table but I would like to compute a column according to another value.
Original Table Columns:
- Id (int)
- FirstName (varchar)
- LastName (varchar)
- Married (bit)
- FamilyName (varchar)
I would like to get from table “Females” the following data, where Last Name should have the original nee surname if married:
SELECT Id, First Name, (CASE WHEN Married = ‘1’ THEN LastName + ‘ Nee ‘ + FamilyName ELSE LastName END) As LastName FROM Females
Result would look like:
|| Duffy Nee Perry
|| Johnson Nee Smith
Where 1 and 4 have Married as False, while 2 and 3 have Married as True
Error / Cause:
In .NET we have Request validation, which is a feature to prevent the server from accepting content from the client side containing un-encoded HTML. It helps to prevent client script-injection attacks submitted to a server for malicious intent. Even thought the intension of such feature is good, it can also cause some issues for us. For instance, I am trying to pass xml data in an input field and the Request validation is preventing the page from proceeding to the server.
- When using up to .NET 2
- When using newer version of .NET
This should do the trick!