Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Stored Procedure date formate

Posted on 2010-09-18
8
Medium Priority
?
192 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 29

Accepted Solution

by:
sammySeltzer earned 2000 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 61

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 61

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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
LVL 9

Author Closing Comment

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

Thank you very much :)
0
 
LVL 29

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 61

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 29

Expert Comment

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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

571 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