Skip navigation
cfinnov
Currently Being Moderated

Handling  date stored in a nvarchar field

Apr 27, 2012 12:36 AM

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?


 
Replies
  • Currently Being Moderated
    Apr 27, 2012 12:42 AM   in reply 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.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 27, 2012 1:20 AM   in reply 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.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 27, 2012 4:26 AM   in reply 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.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 27, 2012 4:56 AM   in reply to cfinnov

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

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 27, 2012 5:22 AM   in reply 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. 

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 27, 2012 9:06 AM   in reply 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 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. 

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points