CODEWITHSUNDEEP
sql-server
Santhana
Santhana

Reputation: 417

SQL SERVER : Date Format DDMMYYYY

String: "FileName-13012018.txt"

How to parse value [13012018] from column1[varchar] and insert into another column2[date] .

I can Parse using substring/ left/right function but i can't able to insert into column2[date].

My goal is to fetch this date from multiple rows and findout max of date.

Column1

FileName-13012018.txt

FileName-14012018.txt

FileName-15012018.txt

result : 15012018

Upvotes: 0

Views: 193

Answers (2)

Zhorov
Zhorov

Reputation: 29943

Another posssible aproach is to remove unnecessery chars and generate valid date:

DECLARE @TmpTable TABLE (
    FFileName varchar(50)
)

INSERT @TmpTable
VALUES 
    ('FileName-13012018.txt'),
    ('FileName-14012018.txt'),
    ('FileName-15012018.txt')

SELECT 
    MAX(DATEFROMPARTS(
        SUBSTRING(REPLACE(REPLACE(FFileName, 'Filename-', ''), '.txt', ''), 5, 4),
        SUBSTRING(REPLACE(REPLACE(FFileName, 'Filename-', ''), '.txt', ''), 3, 2),
        SUBSTRING(REPLACE(REPLACE(FFileName, 'Filename-', ''), '.txt', ''), 1, 2)
    )) AS FMaxDate
FROM @TmpTable

Upvotes: 1

George Menoutis
George Menoutis

Reputation: 7240

The function datefromparts creates a date from its parts.

Considering you have already parsed the "fileName" and "txt" out of the field result, try this:

select max(
            datefromparts(
                            right(result,4), --year
                            substring(result,3,2), --month
                            left(result,2) --day)
                        )
            )
from table_with_parsed_result

Upvotes: 2

Related Questions