CODEWITHSUNDEEP
sql-serversql-server-2000
Icemanind
Icemanind

Reputation: 48696

Extracting date from SQL String

I have a field in my database that contains data like this:

'DUES 1/4/2013'
'DUES 2/18/2013'
'DUES 3/25/2013'
...

What I want to do is extract the date portion of the string and cast it into a valid SQL Date object. How can I do this? I'm using SQL Server 2000.

Upvotes: 2

Views: 173

Answers (1)

Devart
Devart

Reputation: 122002

Try this one -

Query:

DECLARE @temp TABLE (col VARCHAR(50))

INSERT INTO @temp (col)
    SELECT 'DUES 1/4/2013'
    UNION ALL
    SELECT 'DUES 2/18/2013'
    UNION ALL
    SELECT 'DUES 3/25/2013'
    UNION ALL
    SELECT NULL

SELECT 
    [date] = 
        CASE WHEN col IS NOT NULL 
            THEN CAST(SUBSTRING(col, 5, LEN(col)) AS DATETIME) 
        END  
FROM @temp

Results:

date
-----------------------
2013-01-04 00:00:00.000
2013-02-18 00:00:00.000
2013-03-25 00:00:00.000
NULL

Upvotes: 1

Related Questions