12 Replies Latest reply on Oct 9, 2007 1:50 PM by KeithLaw999

    datetime converter

    KeithLaw999 Level 1
      Hi i have a datetime converter to a users local datetime, but i am getting this error

      "{ts '2007-10-07 04:20:21'}" is an invalid date or time string.

      i am not sure why this is?

      <cfset startday = #NOW()#>
      <cfscript>
      tz=createObject("component","timeZone");
      setLocale("English (Australian)");
      smsDate=lsParseDateTime(startday);
      hours=listFirst(SMS_Time,":")+listGetAt(SMS_Time,2,":")/60+listLast(SMS_Time,":")/360;
      smsDate=dateAdd("h",hours,smsDate);
      serverDate=tz.castToServer(smsDate,Australia/Sydney);
      </cfscript>
        • 1. Re: datetime to sql
          Level 7
          KeithLaw999 wrote:
          > Hi i have a datetime field im sql server, i need to insert a datetime but i am
          > getting this error
          >
          > "{ts '2007-10-07 04:20:21'}" is an invalid date or time string.
          >
          > how do i need to convert this for sql server?

          you shouldn't, that's a valid cf datetime. what db driver are you using? can you
          post the code?

          • 2. Re: datetime converter
            BKBK Adobe Community Professional & MVP
            Coldfusion expects the string argument of LSParseDatetime to be "in a format that is readable in the current locale.". Apparently, the string "{ts '2007-10-07 04:20:21'}" is not. Note that parseDateTime(now()) would probably work.

            Two possible solutions are
            <cfset startday = now()>
            <!--- <cfset dt = REreplace(startday, "[ts'{}]","","all")> --->
            <cfset dt = LSDateformat(startday,"yyyy-mm-dd") & " " & LSTimeFormat(startday,"hh:mm:ss")>
            <cfoutput>#LSparsedatetime('#dt#')#</cfoutput>



            • 3. Re: datetime converter
              KeithLaw999 Level 1
              ok thanks i have sorted that now, but the problem is the datetime conversion is incorrect

              if you look at
              http://65.98.61.146/$sitepreview/thesmsengine.com/TestFiles/timetest.cfm

              you will see the time in Sydney Australia is wrong

              any ideas
              • 4. Re: datetime converter
                Level 7
                KeithLaw999 wrote:
                > if you look at
                > http://65.98.61.146/$sitepreview/thesmsengine.com/TestFiles/timetest.cfm

                <cfset startday = #NOW()#>
                <cfscript>
                tz=createObject("component","timeZone");
                setLocale("English (Australian)");
                smsDate=lsParseDateTime(startday);
                hours=listFirst(SMS_Time,":")+listGetAt(SMS_Time,2,":")/60+listLast(SMS_Time,":")/360;
                smsDate=dateAdd("h",hours,smsDate);
                serverDate=tz.castToServer(smsDate,Australia/Sydney);
                </cfscript>

                first startDay is already valid cf datetime value in the server's TZ, no need to
                futz around with anything else. castToServer is designed to take a datetime in
                another TZ & cast to the server's TZ.

                secondly,

                serverDate=tz.castToServer(smsDate,Australia/Sydney);

                the 2nd argument, "Australia/Sydney" has to be a *string* TZ ID.

                serverDate=tz.castToServer(smsDate,"Australia/Sydney");

                maybe you better explain what you want to do.
                • 5. Re: datetime converter
                  KeithLaw999 Level 1
                  hi thanks,

                  all i want to do is insert the current date and time of Sydney/Australia into my table

                  what the best way to do this?
                  • 6. Re: datetime converter
                    Dan Bracuk Level 5
                    quote:

                    Originally posted by: KeithLaw999
                    hi thanks,

                    all i want to do is insert the current date and time of Sydney/Australia into my table

                    what the best way to do this?

                    The best way is with the db's function that returns the current date and time.
                    The 2nd best way is with Cold Fusion's now() function inside a cfqueryparam tag.

                    • 7. Re: datetime converter
                      Level 7
                      KeithLaw999 wrote:
                      > all i want to do is insert the current date and time of Sydney/Australia into my table
                      >
                      > what the best way to do this?

                      assuming the server's *not* in sydney's TZ:

                      <cfscript>
                      tz=createObject("component","timeZone");
                      serverDate=tz.castFromServer(now(),"Australia/Sydney");
                      </cfscript>
                      • 8. Re: datetime converter
                        KeithLaw999 Level 1
                        Hi Paul that works but i also need to get a 2 form strings "form.date" and "form.time"
                        and change them to the servers datetime

                        thanks for your help, i habe done this before but it not working correctly.

                        so if my forms were 12/07/09 10:33:00 i would need to convert this from the users timezone back to the servers datetime.

                        i have tested the code below but the output is wrong
                        • 9. Re: datetime converter
                          Level 7
                          KeithLaw999 wrote:
                          > so if my forms were 12/07/09 10:33:00 i would need to convert this from the

                          what the heck kind of date format is that? it's certainly *not* en_AU. if you
                          get the date format correct this should work:

                          <cfscript>
                          setLocale("en_AU");
                          // no idea what dateformat this is
                          startDay="12/07/09 10:33:00";
                          smsDate=lsParseDateTime(startday);
                          writeoutput("#lsDateFormat(smsDate,'FULL')# #lsTimeFormat(smsDate,'FULL')#");
                          </cfscript>

                          if you can't change the date format but you know the date part order, then use
                          createDateTime() to build a valid cf datetime.

                          • 10. Re: datetime converter
                            KeithLaw999 Level 1
                            ok many thank paul

                            so if i change the datetime format will this code actually change the forms date and time to the eqivilent sever datetime

                            so if my user is in ENGLAND and selects a future date ie 10/11/2009 12:00:00 (UK Datetime)
                            this will then get converted to the servers datetime which would be around 11/11/2009 09:00:00 (Server Datetime)
                            • 11. Re: datetime converter
                              Level 7
                              KeithLaw999 wrote:
                              > so if i change the datetime format will this code actually change the forms
                              > date and time to the eqivilent sever datetime

                              if you need to handle different locales' date formats to build selects,
                              something like this will help:

                              <cffunction access="private" name="buildLocale" output="false" hint="creates
                              valid core java locale from java style locale ID">
                              <cfargument name="thisLocale" required="yes" type="string">
                              <cfscript>
                              var locale=createObject("java","java.util.Locale");
                              var tLocale=locale.getDefault(); // if we fail fallback on server default
                              var l=listFirst(arguments.thisLocale,"_");
                              var c="";
                              var v="";
                              switch (listLen(arguments.thisLocale,"_")) {
                              case 1:
                              tLocale=locale.init(l);
                              break;
                              case 2:
                              c=listLast(arguments.thisLocale,"_");
                              tLocale=locale.init(l,c);
                              break;
                              case 3:
                              c=listGetAt(arguments.thisLocale,2,"_");
                              v=listLast(arguments.thisLocale,"_");
                              tLocale=locale.init(l,c,v);
                              break;
                              }
                              return tLocale;
                              </cfscript>
                              </cffunction>

                              <cffunction access="private" name="getDateTimePattern" output="No"
                              returntype="string" hint="returns locale date/time pattern">
                              <cfargument name="thisLocale" required="yes" type="string">
                              <cfargument name="thisDateFormat" required="no" type="numeric" default="1">
                              <cfargument name="thisTimeFormat" required="no" type="numeric" default="3">
                              <cfscript>
                              var locale=buildLocale(arguments.thisLocale);
                              var tDateFormat=javacast("int",arguments.thisDateFormat);
                              var tTimeFormat=javacast("int",arguments.thisTimeFormat);
                              var
                              tDateFormatter=createObject("java","java.text.DateFormat").getDateTimeInstance(tDateForma t,tTimeFormat,locale);
                              return tDateFormatter.toPattern();
                              </cfscript>
                              </cffunction>

                              <cffunction access="public" name="getDatePartOrder" output="No"
                              returntype="string" hint="returns date part order (day-month-year,
                              month-day-year, etc.) for this calendar/locale">
                              <cfargument name="thisLocale" required="yes" type="string">
                              <cfset var datePartOrder="">
                              <!--- another special case, hong kong mixes chinese chars --->
                              <cfif arguments.thisLocale EQ "zh_HK">
                              <cfset datePartOrder="year month day ampm time">
                              <cfreturn datePartOrder>
                              </cfif>
                              <!--- every place else --->
                              <cfset datePartOrder=getDateTimePattern(arguments.thisLocale,3,3)> <!--- short
                              date/time formats --->
                              <cfset datePartOrder=rereplace(datePartOrder,"a","ampm ","All")> <!--- change
                              ampm --->
                              <cfset
                              datePartOrder=rereplace(datePartOrder,"HH:mm|H:mm|HH.mm|hh:mm|h:mm|h.m.","time","All")>
                              <!--- change date bits --->
                              <cfset datePartOrder=rereplace(datePartOrder,"yyyy|yy","year","All")> <!---
                              change date bits --->
                              <cfset datePartOrder=rereplace(datePartOrder,"MM|M","month","All")> <!---
                              change date bits --->
                              <cfset datePartOrder=rereplace(datePartOrder,"dd|d","day","All")> <!--- change
                              date bits --->
                              <cfset datePartOrder=rereplace(datePartOrder,"/|-|\.|,"," ","All")> <!---
                              change delimiters --->
                              <cfset datePartOrder=rereplace(datePartOrder," "," ","All")> <!--- clean up --->
                              <cfreturn datePartOrder>
                              </cffunction>

                              <cfscript>
                              locales=listToArray("en_US,en_GB,fr_FR,fr_CA,en_CA,th_TH,ar_YE");
                              for (i=1; i LTE arrayLen(locales);i=i+1) {
                              writeoutput("#locales #: #getDatePartOrder(locales)#<br>");
                              }
                              </cfscript>

                              this mess (actually part of a larger CFC that handles i18n stuff) will return a
                              date part order for whatever locales you feed it (has to be java style locale
                              IDs, if you're on cf7 or later you can use those in place of the old,
                              long-winded cf style locales). from that you can arrange your selects in the
                              correct order for your users & correctly lsParseDateTime what they pick into a
                              valid cf datetime.

                              • 12. Re: datetime converter
                                KeithLaw999 Level 1
                                ok thanks Paul, now that has confused me...

                                back to your orginal code below.

                                if this will work what variable do i need to insert into my table?

                                what does setlocal actually do?
                                my server is in Melbourne Australia, if i had a user in Sydney that entered a datetime of 12/07/2009 23:30:00
                                i then need to convert that datetime to 12/07/2009 24:00:00 as Sydney is half hour ahead.

                                but how does the code work out where the user is?

                                <cfscript>
                                setLocale("en_AU");
                                // no idea what dateformat this is
                                startDay="12/07/2009 10:33:00";
                                smsDate=lsParseDateTime(startday);
                                writeoutput("#lsDateFormat(smsDate,'FULL')# #lsTimeFormat(smsDate,'FULL')#");
                                </cfscript>