• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1825
  • Last Modified:

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

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

0
jslaught
Asked:
jslaught
  • 2
  • 2
  • 2
  • +1
1 Solution
 
knightEknightCommented:
Try this:

select request, customer, convert(varchar(10),opened_date,121) as opened_date from issues;
0
 
Pravin AsarPrincipal Systems EngineerCommented:
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.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
jslaughtAuthor 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.
0
 
Brijesh ChauhanStaff IT EngineerCommented:
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;
</cfquery>

<cfoutput> #DateFormat(myrequests.opened_date,'mm/dd/yyyy')# </cfoutput>
0
 
knightEknightCommented:
>> "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;
0
 
jslaughtAuthor Commented:
Thank you for your assistance. It's appreciated.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now