Solved

How do I use the CONVERT function to display a date as mm/dd/yyyy to the user when using datetime format? How do I 'trim' the time off?

Posted on 2007-12-04
3
808 Views
Last Modified: 2010-04-21
I have a field CHARGEDATE that is a datetime format field that displays yyyy-mm-dd hh:mm:ss.xxxx

How do I display this CHARGEDATE field to users as mm/dd/yyyy (?)

I need to incorporate the CONVERT function into an SQL statement that selects records and returns a result....

For example, this statement selects records:

SELECT [ID], [BILLNUMBER], [CHARGEDATE], [LNAME], [FNAME] FROM [TABLETracking] WHERE (([CHARGEDATE] IS NOT NULL) AND ([CHARGEDATE] >= @CHARGEDATE) AND ([CHARGEDATE] <= @CHARGEDATE2) AND ([7CAT] = @7CAT))

This statement converts to yyyy-mm-dd:

SELECT CONVERT(CHAR(10), CHARGEDATE, 120), ...
FROM myTable

How do I:
1.  Incorporate this convert statement into the select statement above so I can run them all at once?
2.  Get the converted date format to show as mm/dd/yyyy   ?

thanks.....







0
Comment
Question by:jazjef
3 Comments
 
LVL 21

Accepted Solution

by:
mastoo earned 500 total points
ID: 20407040
SELECT [ID], [BILLNUMBER], Convert( varchar(10), [CHARGEDATE], 101), [LNAME], [FNAME] FROM [TABLETracking] WHERE (([CHARGEDATE] IS NOT NULL) AND ([CHARGEDATE] >= @CHARGEDATE) AND ([CHARGEDATE] <= @CHARGEDATE2) AND ([7CAT] = @7CAT))
0
 
LVL 4

Expert Comment

by:jindalankush
ID: 20409688
use it select convert(varchar,getdate(),101)
0
 
LVL 4

Author Closing Comment

by:jazjef
ID: 31427354
thanks mastoo!......
solution works great....

JJ
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

778 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