We help IT Professionals succeed at work.

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

Medium Priority
2,544 Views
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;
</cfquery>

Open in new window

Comment
Watch Question

CERTIFIED EXPERT

Commented:
Try this:

select request, customer, convert(varchar(10),opened_date,121) as opened_date from issues;
Pravin AsarPrincipal Systems Engineer
CERTIFIED EXPERT
Top Expert 2005

Commented:
Pravin AsarPrincipal Systems Engineer
CERTIFIED EXPERT
Top Expert 2005

Commented:
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) – yyyy.mm.dd – 2008.10.02          
SELECT convert(varchar, getdate(), 103) – dd/mm/yyyy
SELECT convert(varchar, getdate(), 104) – dd.mm.yyyy
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

Commented:
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.
Staff IT Engineer
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
>> "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

Commented:
Thank you for your assistance. It's appreciated.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.