How can I get date like nov-18 in T-SQL

Question

I need to get SQL date from 2018-11-01 to nov-18.

Personally, I'd worry about the display format in your presentation layer. Formatting a date means changing it from a `date(time)` data type to a `varchar`; which brings its own set of problems. — Thom A, Feb 22 '19 at 13:44

Fahmi · Answer 1 · 2019-02-22T13:37:02.407

4

Use format()

select format(cast('2018-11-01' as date),'MMM-yy', 'en-US')

OUTPUT:

dateval
Nov-18

edited Feb 22 '19 at 13:37

answered Feb 22 '19 at 13:30

Fahmi

I am getting error like this when I tried using this "Argument data type nvarchar(max) is invalid for argument 1 of format "function. – dattu chowdary bolla Feb 22 '19 at 13:31
@dattuchowdarybolla, updated it - you can check now – Fahmi Feb 22 '19 at 13:32
1

`format(cast('2018-11-01' as date),'MMM-yy' ,'en-US')` would be safer in that it will return the specified results irrespective of the language of the login that runs it – Martin Smith Feb 22 '19 at 13:35
1

@MartinSmith, thank u - update it – Fahmi Feb 22 '19 at 13:37
1

Wait, you're storing dates as a `varchar(MAX)` @dattuchowdarybolla ? Why??? – Thom A Feb 22 '19 at 13:40
I got the answer, I converted the date into datetime and later formatted. – dattu chowdary bolla Feb 22 '19 at 13:41
1

@Larnu - even worse. `nvarchar(max)` so it will take double the space (despite never having a non ASCII character) – Martin Smith Feb 22 '19 at 13:49
How to get 2018-08 then? – dattu chowdary bolla Feb 25 '19 at 09:47

1 Answers1