-4

I want to convert date which is in this format '2015-01-25' to a format like this '25-JAN' in sql server 2008 R2

Somebody Help me in this...

gerl
  • 1,161
  • 2
  • 17
  • 53
Waseem Mahmood
  • 19
  • 4
  • 3
    I would highly recommend NOT formatting in your query. Leave the formatting to the front end or you will create a lot more challenges. – Sean Lange Jun 11 '15 at 15:35
  • If you MUST do this in sql, then look at the CONVERT function. https://msdn.microsoft.com/en-us/library/ms187928.aspx – Sean Lange Jun 11 '15 at 15:36
  • almost tried every convert function but does'nt get required format & i need exactly the same format like '25-JAN'...Any Other recomended solution rather than convert ??? – Waseem Mahmood Jun 11 '15 at 15:41
  • You will probably have to roll your own here because that is a rather unique format. Also, think about what happens in the front end here. If you convert this to strings what happens when the user wants to sort this? Or if they have the ability to add some custom filtering? – Sean Lange Jun 11 '15 at 15:42

2 Answers2

3

You will want to use date style 6 for this and then use left function to grab only day and month part like this:

--substitute @date with your own field
DECLARE @date date = '2015-01-25'
SELECT LEFT(convert(VARCHAR, @date, 6), 6)

UPDATE:

Also, if you are looking for a hyphen in between your day and month you could do something like this instead:

--replace space with a hyphen (-)
DECLARE @date date = '2015-06-24'
SELECT REPLACE(RTRIM(convert(VARCHAR(6), @date, 6)),' ','-')
FutbolFan
  • 13,235
  • 3
  • 23
  • 35
  • 1
    change `len(convert(VARCHAR, @date, 6)) - 2)` to 6, there is no need to calculate it since you already know the length needed. – Zohar Peled Jun 11 '15 at 15:53
  • @ZoharPeled I agree, I think I could just define varchar length as 6. Thanks for the suggestion! – FutbolFan Jun 11 '15 at 15:57
3

If this format is for visual purposes like some kind of report, then great, but it is HIGHLY recommended that you do not store dates in any other format but the default YYYY-MM-DD'

Try this out. This adds leading zero for days under 10.

DECLARE @yourTable TABLE(dt DATE)
INSERT INTO @yourTable
VALUES  ('2015-01-25'),
        ('2015-01-01'),
        (GETDATE());

SELECT  dt,
        RIGHT('0' + CAST(DATEPART(DAY,dt) AS VARCHAR(2)),2)
        + '-'
        + LEFT(DATENAME(MONTH,dt),3),
        DATEPART(YEAR,dt) yr --what about year???
FROM @yourTable
Stephan
  • 5,891
  • 1
  • 16
  • 24