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
811 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

696 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