datetime converter

Report · Oct 07, 2007

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>

Report · Oct 07, 2007

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?

Report · Oct 07, 2007

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 = LSDateformat(startday,"yyyy-mm-dd") & " " & LSTimeFormat(startday,"hh:mm:ss")>
<cfoutput>#LSparsedatetime('#dt#')#</cfoutput>

Report · Oct 07, 2007

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

Report · Oct 07, 2007

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.

Report · Oct 07, 2007

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?

Report · Oct 07, 2007

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.

Report · Oct 07, 2007

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>

Report · Oct 08, 2007

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

Report · Oct 08, 2007

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.

Report · Oct 09, 2007

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)

Report · Oct 09, 2007

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(tDateFormat,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="">

<cfif arguments.thisLocale EQ "zh_HK">
<cfset datePartOrder="year month day ampm time">
<cfreturn datePartOrder>
</cfif>

<cfset datePartOrder=getDateTimePattern(arguments.thisLocale,3,3)> 
<cfset datePartOrder=rereplace(datePartOrder,"a","ampm ","All")> 
<cfset
datePartOrder=rereplace(datePartOrder,"HH:mm|H:mm|HH.mm|hh:mm|h:mm|h.m.","time","All")>

<cfset datePartOrder=rereplace(datePartOrder,"yyyy|yy","year","All")> 
<cfset datePartOrder=rereplace(datePartOrder,"MM|M","month","All")> 
<cfset datePartOrder=rereplace(datePartOrder,"dd|d","day","All")> 
<cfset datePartOrder=rereplace(datePartOrder,"/|-|\.|,"," ","All")> 
<cfset datePartOrder=rereplace(datePartOrder," "," ","All")> 
<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.

Report · Oct 09, 2007

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>

Adobe Community

datetime converter