-
1. Re: Handling date stored in a nvarchar field
Owain North Apr 27, 2012 12:42 AM (in response to cfinnov)This is one of those posts I swear people put up just to irritate me, and probably Adam. To be fair, I'd do the same.
Here are your problems:
- You're not using CFQueryParam
- You're storing dates in a varchar field
- You're concentrating on bodging your way around the problem rather than fixing the incorrect datatype
You need to change the datatype of the column. Don't try and re-parse the string into a date, don't try and muck about with it, just change the datatype of the column, because it's wrong. Doing anything else is simply incorrect and a waste of computing resource and your own time.
-
2. Re: Handling date stored in a nvarchar field
cfinnov Apr 27, 2012 1:09 AM (in response to Owain North)I understand that changing the datatype is the correct solution, but its an existing application and we don't have the permission to change the datatype since it affects many other areas. Is there any workaround for this to do the date operations on this field while retreiving the data in a select statement?
-
3. Re: Handling date stored in a nvarchar field
Owain North Apr 27, 2012 1:20 AM (in response to cfinnov)Heh, how many times I've heard that. Some idiot years ago did something wrong, and now people like yourself have to spend hours working around their stupid issues for the rest of time
If you can't change it, how about getting a datetime column *added* for you to use? Shouldn't break any existing applications, you can insert into both columns to keep existing apps working and even write a trigger that parses the inserted varchar dates into the true datetime column. That would be the best thing to do I reckon.
Assuming you can't change it, you'll have to parse the string back as a date. Firstly in ColdFusion, which would only be useful if you didn't need to use it in a select query:
<cfset TS = "{ts '2012-04-27 13:01:18'}" />
<cfset ThisYear = mid(TS, 6, 4) />
<cfset ThisMonth = mid(TS, 11, 2) />
<cfset ThisDay = mid(TS, 14, 2) />
<cfset ThisHour = mid(TS, 17, 2) />
<cfset ThisMinute = mid(TS, 20, 2) />
<cfset ThisSecond = mid(TS, 23, 2) />
<cfset MyDateTimeObj = createDateTime(ThisYear, ThisMonth, ThisDay, ThisHour, ThisMinute, ThisSecond />
You then have a true CF datetime object you can use in your code. I'd strongly recommend writing yourself a helper method that loops through a queryset and converts your varchar results to a datetime so you don't have to repeatedly perform this routine in your code. It also means that if anyone ever fixes the actual issue you just have to change that one function.
However, this doesn't help you if you need to do it in a SELECT query. What database platform are you using? The syntax depends. And please don't say Access.
-
4. Re: Handling date stored in a nvarchar field
BKBK Apr 27, 2012 4:26 AM (in response to Owain North)Owain North wrote:
Some idiot years ago did something wrong, and now people like yourself have to spend hours working around their stupid issues for the rest of time
Well said, Owain. More relevant to software than Murphy's laws.
-
5. Re: Handling date stored in a nvarchar field
BKBK Apr 27, 2012 4:56 AM (in response to cfinnov)cfinnov wrote:
in the select query
'DATEDIFF(M,getdate() ,CONVERT(VARCHAR(50), ,mydatetime ) , 121))',but didn't work out.
There is an error in your function definitions. You've given the dateDiff function 4 parameters, whereas it should have 3. Also, the 'expression' is missing from the convert function. (I have assumed you're on SQL Server).
-
6. Re: Handling date stored in a nvarchar field
Dan Bracuk Apr 27, 2012 5:22 AM (in response to cfinnov)Regarding:
'DATEDIFF(M,getdate() ,CONVERT(VARCHAR(50), ,mydatetime ) , 121))',but didn't work out.
How can we handle this?I'd have to look up the syntax for convert to check everything, but I did notice two consecutive commas.
-
7. Re: Handling date stored in a nvarchar field
-==cfSearching==- Apr 27, 2012 9:06 AM (in response to cfinnov)So the data is saved like a string {ts '2012-04-27 13:01:18'}.
While retreiving the data, it seems difficult to do any date operations on this field like datediff.Tried to manage with convert function like this in the select queryTo answer your question, sql server's convert function does not understand CF's ODBC date string. You need to remove the starting "{ts '" and closing "}". So you're left with '2012-04-27 13:01:18'. You can then convert that string to a datetime value using style 120 ie ODBC.
SELECT dateDiff(M, getDate(), convert(datetime, '2012-04-27 13:01:18', 120))
I agree using a datetime column is the much better approach (but that was already discussed ad nauseum ;-) If it is truly not possible to change the column type, an interim solution might be to create a "view" that parses the ODBC string into a datetime object. Then SELECT from the "view" instead of the table.