SQL Server : split string in SELECT statement

Question

I want to split date in column in 3 fields, I use this query

SELECT  
SUBSTRING(Account.date, 1, 2) AS "Month",
SUBSTRING(Account.date, 4, 2) AS "Day",
SUBSTRING(Account.date, 7, 4) AS "Year"
FROM Account

Almost all data is in format 02/11/2000, but some of it can be 02/November/2000 or 2/11/2000.

Only common thing is that data separated by /. How can I separate this column using the delimiter?

How do you know if "02/11/2000" is November 2, 2000 or February 11, 2000? — Jeremy Hutchinson, Apr 28 '14 at 12:30
You should convert Account.date to datetime and then use inbuilt function Year(), Month, Day to get result. — Deepshikha, Apr 28 '14 at 12:34
@jrhutch , I don't. I always assume that sequence is day/month/year — user3260664, Apr 28 '14 at 12:55
A pretty common thing that I have to deal with, as Accounting Period dates for our third party software are stored as a string value (they can't be a date, as the "13th month" is a possible accounting period--even if they could, I have no control over the types of their fields). GarethD's answer seems the cleanest for my needs. — Jon, Dec 12 '14 at 20:04

score 4 · Answer 1 · answered Apr 28 '14 at 12:32

4

You can do it this way by using CHARINDEX and SUBSTRING functions

select 
    LEFT(Account.date, CHARINDEX('/', Account.date) - 1),
    SUBSTRING(Account.date, CHARINDEX('/', Account.date) + 1, LEN(Account.date) - CHARINDEX('/', Account.date) - CHARINDEX('/', Account.date, CHARINDEX('/', Account.date)) - 2),
    REVERSE(LEFT(REVERSE(Account.date), CHARINDEX('/', REVERSE(Account.date)) - 1))
FROM Account

answered Apr 28 '14 at 12:32

Szymon

42,577
16
96
114

`RIGHT(Account.date, CHARINDEX('/', REVERSE(Account.date)) - 1)` . . . For the third part. Great answer, I used this style with just that minor change – Suamere Apr 03 '17 at 22:09

Mudassir Hasan · Accepted Answer · 2014-04-28T12:40:56.357

4

Surprisingly CAST('2/November/2000' as datetime) works (checked on SQL Server 2008), gives value 2000-11-02 00:00:00.000

SELECT  
Month(CAST(Account.date AS DateTime)) "Month",
Day(CAST(Account.date AS DateTime)) "Day",
Year(CAST(Account.date AS DateTime)) "Year",

FROM Account

But as rightly pointed out in comment how do you know if "02/11/2000" is November 2, 2000 or February 11, 2000?

Also the spelling of Month names must be absolutely correct else conversion fails. Since you are storing dates as string there is chance that entry like November , Agust etc could have been made .

You should never store date values as strings.

edited Apr 28 '14 at 12:40

answered Apr 28 '14 at 12:35

Mudassir Hasan

28,083
20
99
133

1

Works fine if you make sure that date format is `dmy`. Add `set dateformat dmy;` before the query to make sure. +1. – Mikael Eriksson Apr 28 '14 at 12:51
yes @MikaelEriksson .. without doing set dateformat , a value like 17/01/2000 will throw error conversion failed..thnks for pointing out – Mudassir Hasan Apr 28 '14 at 12:53
Excellent answer, well explained. – Alec. Apr 28 '14 at 13:01

score 1 · Answer 3 · edited Jul 29 '20 at 18:28

You can abuse the PARSENAME function slightly here:

SELECT  FirstPart = PARSENAME(REPLACE(Account.Date, '/', '.'), 3),
        SecondPart = PARSENAME(REPLACE(Account.Date, '/', '.'), 2),
        ThirdPart = PARSENAME(REPLACE(Account.Date, '/', '.'), 1)
FROM    (VALUES
            ('02/November/2000'),
            ('2/11/2000')
        ) Account (Date);

Will give:

FirstPart   SecondPart  ThirdPart
02          November    2000
2           11          2000

I would however, highly recommend storing your dates using the appropriate data type!. SQL Server 2012 has the TRY_CONVERT function which can make such conversions easier, but you still need to know what format your string date is in, 2/11/2000 could be the 2nd November, or 11th February depending on your regional settings.

score 0 · Answer 4 · edited May 23 '17 at 12:18

0

You can use the combination of CharIndex and Substring function in SQL to split the string based on delimiter.

You can check CharIndex examples here SQL Server 2005 Using CHARINDEX() To split a string and here SQL Server - find nth occurrence in a string

edited May 23 '17 at 12:18

Community

1
1

answered Apr 28 '14 at 12:30

Nickolay Komar

240
1
8

score 0 · Answer 5 · edited May 23 '17 at 11:33

Assuming your database column account.date contains a valid datetime or date value you should using SQLServers date functions like:

select month(getDate()) as "Month", 
       day(getDate()) as "Day", 
       year(getDate()) as "Year"

I replaced your column account.date by a getDate() to have some test values. This maps to your SQL in the following way

SELECT  
month(Account.date) AS "Month",
day(Account.date) AS "Day",
year(Account.date) AS "Year"
FROM Account

Storing these date values as varchars would be IMHO a design flaw of your database structure. Dates are formatted in multiple ways to text. This is the presentation of your database data. To process your data you would always preprocess your text dates. That is bad practice.

If you have indeed varchar values there are several SO questions like: How do I split a string so I can access item x?.

score 0 · Answer 6 · edited Dec 22 '17 at 10:34

0

Try this:

DECLARE @VALUE VARCHAR(100)<BR>
SET @VALUE ='2/11/2000' <BR>
SELECT SUBSTRING(@VALUE,0,CHARINDEX('/',@VALUE,0)),

SUBSTRING(@VALUE,
CHARINDEX('/',@VALUE,0)+1,

(CHARINDEX('/',@VALUE,(CHARINDEX('/',@VALUE,0)+1)) - 
CHARINDEX('/',@VALUE,0) - 1)

),RIGHT(@VALUE,4)

edited Dec 22 '17 at 10:34

Bob

5,510
9
48
80

answered Apr 28 '14 at 14:09

Balaji

112
2

SQL Server : split string in SELECT statement

6 Answers6