Solved

SQL Stored Procedure date formate

Posted on 2010-09-18
8
180 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

832 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