Solved

SQL Datetime to varchar in 'M/d/yyyy' format

Posted on 2006-06-20
7
1,117 Views
Last Modified: 2008-01-09
Hello experts,

How would I go about converting a datetime value into a date of format 'M/d/yyyy' (i.e. no zeros so: 1/1/2006 or 11/20/2006)?

Thanks
0
Comment
Question by:TLevin10
[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
7 Comments
 
LVL 13

Assisted Solution

by:Atlanta_Mike
Atlanta_Mike earned 150 total points
ID: 16945316
One way is:

SELECT datepart(mm,DateField) + '/' + datepart(dd,DateField) + '/' + datepart(yyyy,DateField)
FROM TableName

Another is:
SELECT REPLACE(convert(varchar(15),DateField,101),'0','')
FROM TableName

You'll have to test to see which is most efficient for your process.
0
 

Author Comment

by:TLevin10
ID: 16945871
Actually, neither one is correct...

The first one has problems concatenating int and string types, while the second one cuts out ALL the zeros so '10/20/2005' becomes '1/2/25' which is clearly incorrect...

However, your first one did remind me that I could do it manually using the basic datepart functions.  The correct solution is below, but I'm hoping that maybe there is a better solution somewhere?

Yes, I know this is the correct answer, I'm wondering if there is something better/shorter/easier, since I have to do this multiple times for different fields

=================
SELECT CONVERT(varchar(2),datepart(mm,DateField)) + '/' + CONVERT(varchar(2),datepart(dd,DateField)) + '/' + CONVERT(varchar(4),datepart(yyyy,DateField))
FROM TableName
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16947641
This is best accomplished using your front-end application and not rely on SQL Server's rudimentary formatting.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:TLevin10
ID: 16947667
actually, this is going to be done in a C# RowFilter on the client side - thats why the formatting has to be handled as such - I built a "search" tool which operates on a similar principal to programs like iTunes, which filters dynamically on all columns (including datetime columns) - right now, it correctly matches dates only if they have zeros in them (such as '6/03/2006') becuse of the way the CONVERT function works (the C# rowfilter operates on the same principals as a SQL 'where' statement.  The code I am using right now is:

dataView1.RowFilter = string.Format("CONVERT(title, System.String) LIKE '*{0}*') OR (CONVERT(dateOrdered, System.String) LIKE '*{0}*') OR (CONVERT(dateDue, System.String) LIKE '*{0}*'", userWord);

Hope that helps explain why I'm looking for this type of formatting and need it to be short...
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 100 total points
ID: 16949313
some thing like this


select cast(month(getdate()) as varchar) +'/'+cast(day(getdate()) as varchar)+'/'+cast(year(getdate()) as varchar)



select cast(month(DateField) as varchar) +'/'+cast(day(DateField) as varchar)+'/'+cast(year(DateField) as varchar)
from yourTable
0
 

Author Comment

by:TLevin10
ID: 16950550
thats the same as the original answer I gave, just using cast instead of convert, and the shorthand notations for datepart, but thanks :)
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16969203
Damn... you're right... forgot the cast..and my brain was missing in the second... duh.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

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 …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

688 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