Converting Datatypes: nvarchar to datetime
-
Hey All,
I am in an interesting situation. I have had a database provided to me via .bak files that seems to have the datatypes "confused" when it is restored. the original DB was a SQL server 7 db, the system the new DB is being restored into is SQL2k. For some reason it seem that when restored, datetime fields end up as nvarchar and smalldatetime fields come back as nchar.
I really need to get those datatypes back to datetime as I am getting all kinds of "Syntax error converting datetime from character string" when trying to do simple >= comparison query's based on todays's date. Ironically I get the same error when trying to convert the datatype in the enterprise manager. Am I S.O.L. ? Anyone have any Ideas on what to do?
-
I have not come across this one before but I would guess that the confusion is caused by the source and target machines being set to different locales
British Date DMY
American Date MDY
Unix Date YMD
Normally if you have two different collation sequences the backup from one simply refuses to restore.
Converting characters to dates can be achieved with either CONVERT or CAST.
I prefer CONVERT because you can specify what format your source date is in, where as CAST makes assumptions.
-
Hi David, thanks for your Quick reply.
I dont think the machines are set to different locales as they are both US based machines, but I will certainly look into that.
I have tried Cast and Convert a few times, however I have not had any success. (although I was using them in SELECT querys). I would always get the Error I listed above. Are you suggesting using cast/convert in an ALTER TABLE statement (bear with me please, I am still a newbie
). I've listed a couple queries that worked fine on the 7 box, but throw errors on the 2K box below to see if that helps clarify what I am trying to do. (note I am using ColdFusion as my web API)cfset today_int="#createODBCDate(now())#"
cfset w_cost_low = #form.price# - 50
cfset w_cost_high = #form.price# + 50
cfquery name="get_getaways" datasource="crivacation_final"
SELECT *
FROM tblgetaway
WHERE w_cost>#w_cost_low# AND w_cost#today_int#
ORDER BY w_cost
/cfquery
I have used cast on the today_int var to recast as nvarchar, but obviously my date comparisons dont work on strings. When i use cast or convert on the start_dt field I get the dreaded error I listed above.
Any thoughts? (and thanks again for the post!)
-
Firstly, we always call stored procedures from our ColdFusion Apps
If in any doubt at all we pass the dates into the procs as varchars but in a known format. The front end app enforces that format.
We do this using a client side Javascript RegEx which we got from RegExLig.com I think the original was for American dates M/D/Y
function ValidateDate(theDateField) { /* This function validates dates entered in d/m/yy or dd/mm/yyyy format. You can also use the - character as you delimiter. The function correctly evaluates days of the month including leap years. Code Written by Joe Gresham Sept 2003 RegEx Supplied by RegExLib.com */ var dummy var theEx var theDate = new String(); theDate = theDateField.value; theEx = /^(?:(?:31(\/|-|\.)(?:0?[13578]|1[02]))\1|(?:(?:29|30)(\/|-|\.)(?:0?[1,3-9]|1[0-2])\2))(?:(?:1[6-9]|[2-9]\d)?\d{2})$|^(?:29(\/|-|\.)0?2\3(?:(?:(?:1[6-9]|[2-9]\d)?(?:0[48]|[2468][048]|[13579][26])|(?:(?:16|[2468][048]|[3579][26])00))))$|^(?:0?[1-9]|1\d|2[0-8])(\/|-|\.)(?:(?:0?[1-9])|(?:1[0-2]))\4(?:(?:1[6-9]|[2-9]\d)?\d{2})$/ dummy = theDate.search(theEx); if(theDate!='') { if(dummy !=-1) { return true; } else { alert(theDate + " is not a valid date!"); theDateField.value = ""; return false; } } }
As we always know what format the date going into the proc is, one of the first tasks that the proc does is to CONVERT the paramater value into a DATETIME variable for use further down the procedure.
I am sure you know all the arguments for using stored procedures rather than dynamic SQL. I was doing the SQL mentoring to a ColdFusion developer and his comments were that the ColdFusion syntax for calling a stored procedure actually made his code easier to understand.
Dates in SQL Server are a pain. I just wish that whoever first wrote SQL server had stuck with yyyymmdd hhmmssmmmm
-
Dates in SQL Server are a pain.
Really?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks for keeping up on this post!
I too prefer to call stored procs from CF. It definately simplifies code and keeps the hard work on the DB and not the application server.
Unfortunately I am dealing with someone else's legacy code and it is fairly antiquated. Sort of "doing a friend a favor" here, which of course has turned out to be a pain in the butt.
The above solutions are excellent moving forward (and that regex code is pretty sweet, thanks) but still have the main problem. I need to convert the actual column datatype to datetime as there is TON of legacy code that refers to that column under the assumption its date time.
I think I may have to write a query that will pull all the data from that table, create a new table with the appropriate datatypes and then insert all the records again.
If there is a better way to do this please let me know.
thanks!
Patrick
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply