Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


SQL Stored Procedure date formate

Posted on 2010-09-18
Medium Priority
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

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

Question by:Evan Cutler
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
LVL 29

Accepted Solution

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

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

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

LVL 60

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:

REPLACE('d-m-yyyy', 'm', MONTH(getdate())),
'd', DAY(getdate())),
'yyyy', YEAR(getdate()))
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

LVL 11

Expert Comment

ID: 33709359
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))

Author Closing Comment

by:Evan Cutler
ID: 33709376

Thank you very much :)
LVL 29

Expert Comment

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.
LVL 60

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.
LVL 29

Expert Comment

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

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
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 course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

721 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