We help IT Professionals succeed at work.

CreateODBCDateTime and Euro Date

tsherr
tsherr asked
on
I have a CF database and am entering dates in Euro format (dd/mm/yyyy.)  When I use CreateODBCDateTime, it converts the date into YYYY/MM/DD TT format, but it screws the date up, swapping the day and month around.  I have the correct format set up in Access.  Is there a way to fix this?
Comment
Watch Question

Commented:
<cfoutput>
#CreateOdbcDate("#your_date_here#","dd/mm/yyyy")#
</cfoutput>

I this what your are using?

Commented:
"I" should be "Is"....Sorry
Yog

Commented:
1. check out the date format you have choosed in language and regional options..

2. if that doesn't work, use
<Cfoutput>
     #dateformat("#now()#","dd/mm/yyyy")#
</cfoutput>

Author

Commented:
" <cfoutput>
#CreateOdbcDate("#your_date_here#","dd/mm/yyyy")#
</cfoutput> "

When I try this, I get the error: "The function CreateODBCDateTime takes 1 arguments(s)" so that doesn't appear to work.

I have the date set to the dd/mm/yyyy format in regional settings in Windows, and in Access.

So my code looks like this:

<CFSET FirstDate = #CREATEODBCDATETIME(StartDate)#>
<CFSET LastDate = #CREATEODBCDATETIME(EndDate)#>

<cfquery datasource="pilots" name="GetFormInfo">
     SELECT      [Fields in here]              
                [Joins here]
     WHERE
     ((Forms.AssignmentDate) BETWEEN #FirstDate#
     AND #LastDate#);
</cfquery>

The problem is in the last section.  Forms.AssignmentDate comes up in the format YYYY/MM/DD HH:MM:SS but my FirstDate and LastDate come up as YYYY/MM/DD HH:MM:SS if the MM is greater than 12 in my entry field, but if it is less than 12, it swaps the MM and the DD.

Can I format the Forms.AssignmentDate in an SQL Query?

T

Commented:
Sorry actualy I wanted to write dateforamt instead of CreateODBCDate

Author

Commented:
So rather than using CreateODBCDateTime to create my variables, use DateFormat?  Since my Forms.SessionDate is coming up with the format YYYY/MM/DD HH:MM:SS (or 2002/01/12 00:00:00), would I concatenate the time onto the end of my FirstDate and LastDate variable?  If so, how would I do that?

T
Commented:
Use DateFormat and TimeForamt Together.

#DateFormat("#ur_date#","dd/mm/yyyy")##TimeFormat("#ur_time#","hh:mm:ss")#

HTH

Jimmy
Yog

Commented:
try

SELECT      [Fields in here]              
               [Joins here]
    WHERE
    Forms.AssignmentDate >= #createodbcdate(FirstDate)#
    AND
    Forms.AssignmentDate <= #createodbcdate(LastDate)#

i think date is enough for comparision, and this will compare..

...

Explore More ContentExplore courses, solutions, and other research materials related to this topic.