Skip to main content
jeffw2002
jeffw2002
Inspiring
February 24, 2012
Answered

Query of a Query Extract Time from DateTime Field

  • February 24, 2012
  • 4 replies
  • 1522 views

Thanks in advance for your help! 

How do I extract time from a datetime field in a Query of a Query and sort the results by the time?  "aDateTime" is a datetime field in an MS-SQL database.  We are using ColdFuson 5.

<cfquery name="anSQLquery" datasource="#aDataSource#" dbtype="odbc">

Select *

from aTable

</cfquery>

ATTEMPT 1:

<cfquery dbtype="query" name="aQueryOfQuery">

select *,

  cast(aDateTime as time) as aTime

from anSQLquery

order by

  aTime

</cfquery>

<cfdump var="#aQueryOfQuery#">

RESULT:

aTime

{ts '2012-02-27 08:00:00'}

Which is not what is wanted, which is something like:

{ts '1900-01-01 08:00:00'} or

{ts '08:00:00'} or

'08:00:00' or

'08:00'

Other attempts:

datepart(BEGIN_DATE_TIME, "HH:mm") as MeetTime

right(cast(BEGIN_DATE_TIME as varchar),8) as MeetTime

Result in errors.

Thanks,

Jeff

    This topic has been closed for replies.
    Correct answer jeffw2002

    One Solution:

    <cfquery dbtype="query" name="aQueryOfQuery">

    select *,

      cast(cast(aDateTime as double) - cast(aDateTime as integer) as date) as aTime

    from anSQLquery

    order by

      aTime

    </cfquery>

    4 replies

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    February 25, 2012

    I am with Dan here. Why go into query of a query when query alone is sufficient? In MS SQL, you could do something along the lines of

    <cfquery name="anSQLquery" datasource="#aDataSource#">

    Select *, datePart(hh, aDateTime) as hours, datePart(mi, aDateTime) as minutes

    from aTable

    order by hours, minutes

    </cfquery>

    or, arguably better,

    <cfquery name="anSQLquery" datasource="#aDataSource#">

    Select *, convert(time(0), aDateTime, 108) as the_time

    from aTable

    order by the_time

    </cfquery>

    jeffw2002
    jeffw2002Author
    Inspiring
    February 27, 2012

    HI Dan,

    ColdFusion5 has a limited set of SQL functions that will work in a query of a query:  I can't speak to ColdFusion9.  I could not get the functions you suggested to work.  The "One Solution" I came up with works though... happily.

    Thanks,

    Jeff

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    February 27, 2012

    jeffw2002 wrote:

    HI Dan,

    ColdFusion5 has a limited set of SQL functions that will work in a query of a query:  I can't speak to ColdFusion9.  I could not get the functions you suggested to work.  The "One Solution" I came up with works though... happily.

    I can imagine how difficult it is to plough along with Coldfusion 5. Could you please share the solution with us (oh, and to mark the question as answered)?

    D
    Dan_Bracuk
    Inspiring
    February 25, 2012

    I suggest doing it in your original query.  The specifics depend on the db type you are using.

    jeffw2002
    jeffw2002Author
    Inspiring
    February 27, 2012

    The original query can't be changed (easily).

    thanks,

    jeff

    jeffw2002
    jeffw2002AuthorCorrect answer
    Inspiring
    February 24, 2012

    One Solution:

    <cfquery dbtype="query" name="aQueryOfQuery">

    select *,

      cast(cast(aDateTime as double) - cast(aDateTime as integer) as date) as aTime

    from anSQLquery

    order by

      aTime

    </cfquery>

    jeffw2002
    jeffw2002Author
    Inspiring
    February 24, 2012

    edit:

    Other attempts:

    datepart(BEGIN_DATE_TIME, "HH:mm") as aTime

    right(cast(BEGIN_DATE_TIME as varchar),8) as aTime

    Result in errors.

    Thanks,

    Jeff

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices