Copy link to clipboard
Copied
Hi,
The values to one of our existing tables' field is saved like this.
<cfset mydatetime = createodbcdatetime(now())>
<cfdump var="#mydatetime#">
<cfquery .... >
INSERT INTO yourTable (Columnname)
VALUES (#myDateTime#)
</cfquery>
The problem is that datatype of the field is nvarchar instead of date time. 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 query
'DATEDIFF(M,getdate() ,CONVERT(VARCHAR(50), ,mydatetime ) , 121))',but didn't work out.
How can we handle this?
Copy link to clipboard
Copied
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 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.
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
cfinnov wrote:
While retreiving the data, it seems difficult to do any date operations on this field like datediff.Tried to manage with convert function like thisin 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).
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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 query
To 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.