How to get a date part from a string in sql server

Question

'UNK-JAN-2015' This is my date value. UNK refers Unknown value. Now I need to get only month from that. Is there any way to find it......

YOu are storing dates as string so You can use the `Right`. https://msdn.microsoft.com/en-us/library/ms177532.aspx I will advice you to use the proper datatypes for the data. — Mahesh, Mar 10 '15 at 10:36

SouravA · Answer 1 · 2015-03-10T11:42:15.827

3

SELECT 
RIGHT(DateString, 4) [Year], 
LEFT(RIGHT(DateString, 8), 3) [Month] 
FROM YourTable

edited Mar 10 '15 at 11:42

answered Mar 10 '15 at 10:36

SouravA

5,147
2
24
49

Ullas · Answer 2 · 2015-03-10T11:48:06.450

1

Using SUBSTRING

Query

SELECT SUBSTRING('UNK-JAN-2015',LEN('UNK-JAN-2015')-3,4);

Using RIGHT

Query

SELECT RIGHT('UNK-JAN-2015', 4);

To get Month from the given string,

Query

SELECT SUBSTRING('UNK-JAN-2015',5,3) AS [Month];

edited Mar 10 '15 at 11:48

answered Mar 10 '15 at 10:40

Ullas

11,450
4
33
50

please view the edit to the post and update accordingly, as the OP s now requesting the month – Tanner Mar 10 '15 at 11:35

Tim Schmelter · Answer 3 · 2015-03-10T11:06:52.373

0

If you instead want to find the last token separated by - you could use this:

SELECT DT = REVERSE(SUBSTRING(REVERSE(@DateString),0,CHARINDEX('-',REVERSE(@DateString))))

This also returns 2015, so the text behind the last dash. So it does not only return the last 4 characters. With UNK-JAN-2015/02/09 it returns 2015/02/09.

Edit: If you want the middle part, the month, you could use:

SELECT SUBSTRING(@DateString, CHARINDEX('-',@DateString)+1,LEN(@DateString)-CHARINDEX('-',@DateString,CHARINDEX('-',@DateString))-(CHARINDEX('-',@DateString)+1))

edited Mar 10 '15 at 11:06

answered Mar 10 '15 at 10:42

Tim Schmelter

450,073
74
686
939

Thanks for you answer. How to get only month value? – Jasper Mar 10 '15 at 10:48
@Jasper: i've edited my answer. Maybe there's an easier approach but that works and is completely dynamic so only looks for the `-`. – Tim Schmelter Mar 10 '15 at 11:07

score 0 · Answer 4 · edited Mar 10 '15 at 11:06

0

Query

declare @text varchar(100)
set @text='UNK-JAN-2015'

set @text= '<r>'+ REPLACE(@text,'-','</r><r>') + '</r>'

select i.value('r[2]','varchar(10)') AS Year 
from (select cast(@text as xml) xml)out CROSS APPLY out.xml.nodes('/') x(i)

edited Mar 10 '15 at 11:06

Ullas

11,450
4
33
50

answered Mar 10 '15 at 11:03

Vindhyachal Kumar

1,713
2
23
27

score 0 · Answer 5 · answered Mar 10 '15 at 11:11

To get an actual month value, rather than simply taking the 3 characters that make up the month portion of the string, you can convert it to a date using some string manipulation and extract the month from it:

DECLARE @val NVARCHAR(15) = 'UNK-JAN-2015'

SELECT  CONVERT(DATETIME, RIGHT(@val, 4) + '-' + SUBSTRING(@val, 5, 3)) AS TheDate,
    MONTH(CONVERT(DATETIME, RIGHT(@val, 4) + '-' + SUBSTRING(@val, 5, 3))) MonthNumber,
    DATENAME(MONTH,
           CONVERT(DATETIME, RIGHT(@val, 4) + '-' + SUBSTRING(@val, 5, 3))) [MonthName]

Output:

TheDate                   MonthNumber   MonthName
2015-01-01 00:00:00.000   1             January

This assumes that the value is always in the same format: XXX-MMM-YYYY (where XXX is some prefix)

How to get a date part from a string in sql server

5 Answers5