7 Replies Latest reply: Apr 27, 2012 9:06 AM by -==cfSearching==- RSS

    Handling  date stored in a nvarchar field

    cfinnov

      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?


        • 1. Re: Handling  date stored in a nvarchar field
          Owain North Community Member

          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 Community Member

            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 Community Member

              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 CommunityMVP

                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 CommunityMVP

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

                  • 6. Re: Handling  date stored in a nvarchar field
                    Dan Bracuk Community Member

                    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==- Community Member

                      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.