How do I trim/cut out or remove the hh:mm:ss: from date/time displayed on a coldfusion page

Posted on 2011-04-22
Last Modified: 2012-05-11
Hello. I was not able to find quite what I was lookig for in an answer via the experts-exchange solutions database or on the regular internet so I decided to submit a question to the experts.

I am developing a coldfusion page that displays query results from a access database that includes the date entry of records. The only problem is that the time is also being displayed with the date (for example: 2011-04-22 09:45:37:00). I would like to not have the time displayed and have tried to use substr or cast or convert or the trim functions within the sql query to no avail. Below is the <cfquery> that i'm using.

Your help is appreciated in advance.

<cfquery name="myrequests" datasource="srs">
select request, customer, opened_date from issues;

Open in new window

Question by:jslaught
    LVL 33

    Expert Comment

    Try this:

    select request, customer, convert(varchar(10),opened_date,121) as opened_date from issues;
    LVL 28

    Expert Comment

    by:Pravin Asar
    LVL 28

    Expert Comment

    by:Pravin Asar
    I found my saved SQL 101 Notes.

    – Microsoft SQL Server T-SQL date and datetime formats
    – Date time formats – mssql datetime
    – MSSQL getdate returns current system date and time in standard internal format
    SELECT convert(varchar, getdate(), 100) – mon dd yyyy hh:mmAM (or PM) – Oct  2 2008 11:01AM          
    SELECT convert(varchar, getdate(), 101) – mm/dd/yyyy - 10/02/2008                  
    SELECT convert(varchar, getdate(), 102) – – 2008.10.02          
    SELECT convert(varchar, getdate(), 103) – dd/mm/yyyy
    SELECT convert(varchar, getdate(), 104) –
    SELECT convert(varchar, getdate(), 105) – dd-mm-yyyy
    SELECT convert(varchar, getdate(), 106) – dd mon yyyy
    SELECT convert(varchar, getdate(), 107) – mon dd, yyyy
    SELECT convert(varchar, getdate(), 108) – hh:mm:ss
    SELECT convert(varchar, getdate(), 109) – mon dd yyyy hh:mm:ss:mmmAM (or PM) – Oct  2 2008 11:02:44:013AM  
    SELECT convert(varchar, getdate(), 110) – mm-dd-yyyy
    SELECT convert(varchar, getdate(), 111) – yyyy/mm/dd
    SELECT convert(varchar, getdate(), 112) – yyyymmdd
    SELECT convert(varchar, getdate(), 113) – dd mon yyyy hh:mm:ss:mmm  – 02 Oct 2008 11:02:07:577    
    SELECT convert(varchar, getdate(), 114) – hh:mm:ss:mmm(24h)
    SELECT convert(varchar, getdate(), 120) – yyyy-mm-dd hh:mm:ss(24h)
    SELECT convert(varchar, getdate(), 121) – yyyy-mm-dd hh:mm:ss.mmm
    SELECT convert(varchar, getdate(), 126) – yyyy-mm-ddThh:mm:ss.mmm – 2008-10-02T10:52:47.513

    Hope this helps.

    Author Comment

    Neither suggestion listed above worked. I am getting an Error Executing Database Query message. It's saying "Circular reference caused by alias 'opened_date' in query definition's SELECT list". Not sure what this means.
    LVL 11

    Accepted Solution

    if you only want to display date, then use  DATEFORMAT while displaying the records...

    <cfquery name="myrequests" datasource="srs">
    select request, customer, opened_date from issues;

    <cfoutput> #DateFormat(myrequests.opened_date,'mm/dd/yyyy')# </cfoutput>
    LVL 33

    Expert Comment

    >> "Circular reference caused by alias 'opened_date' in query definition's SELECT list"

    I didn't realize you were running this against Access.  The alias, or label, of a calculated field cannot be identical to any of the field names used to calculate the field.  To resolve this, simply change the alias:

    select request, customer, convert(varchar(10),opened_date,121) as MY_Opened_Date from issues;

    Author Closing Comment

    Thank you for your assistance. It's appreciated.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now