Solved

SQL Stored Procedure date formate

Posted on 2010-09-18
8
176 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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 video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

707 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

18 Experts available now in Live!

Get 1:1 Help Now