Solved

SQL Stored Procedure date formate

Posted on 2010-09-18
8
177 Views
Last Modified: 2012-05-10
The segment below works great:
SELECT convert(varchar, t1.Show_Date, 105))

it gives me dates like 06-09-2010

Question...
what would it take to give me 6-9-2010?

Thanks
0
Comment
Question by:Evan Cutler
8 Comments
 
LVL 28

Accepted Solution

by:
sammySeltzer earned 500 total points
ID: 33709340
Maybe something like this?

SELECT REPLACE(
REPLACE(
REPLACE('m-d-yyyy', 'm', MONTH(t1.Show_Date)),
'd', DAY(t1.Show_Date)),
'yyyy', YEAR(t1.Show_Date))
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33709341
You will probably have to construct it manually to achieve that.  Replace the following with your actual column name instead of getdate().
select datename(dd, getdate())+'-'+convert(varchar(2), datepart(mm, getdate()))+'-'+datename(yy, getdate());

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33709350
That is a interesting way to do that @sammySeltzer.  Per the question format, I think you meant like this though:

SELECT REPLACE(
REPLACE(
REPLACE('d-m-yyyy', 'm', MONTH(getdate())),
'd', DAY(getdate())),
'yyyy', YEAR(getdate()))
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 33709359
SELECT
CAST(DATEPART(day, t1.Show_Date) as varchar(2)) + '-' +
CAST(DATEPART(month, t1.Show_Date()) as varchar(2)) + '-' +
CAST(DATEPART(year, t1.Show_Date) as varchar(4))
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 9

Author Closing Comment

by:Evan Cutler
ID: 33709376
PERFECT!!!!

Thank you very much :)
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 33709379
Actually, I meant it the way I did it because he has a date field called Show_Date.

He can certainly change it to getdate()...

Either way will work.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33709386
@sammySeltzer, as you will see what I highlighted was the date format.  It was m-d-yyyy, but the question asked for d-m-yyyy.  Nothing wrong with your approach otherwise.  I liked that it took advantage of the implicit conversion to varchar for the replace function.  Nice work!  I don't have the table fields on my system, so to run your code I had to use getdate() -- that's why that was there -- not an indication it needed to be that.
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 33709431
@mwvisa1, thanks for the clarification and thanks for the compliment.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

948 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

20 Experts available now in Live!

Get 1:1 Help Now