Handling date stored in a nvarchar field

[Guest]

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?

[Owainnorth]

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.

[Guest]

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?

[Owainnorth]

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.

[BKBK]

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.

[BKBK]

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 this

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).

[Dan_Bracuk]

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. 

[-__cfSearching__-]

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.