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
804 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

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 …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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

744 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

11 Experts available now in Live!

Get 1:1 Help Now