[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
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
Medium Priority
?
815 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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

873 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