CODEWITHSUNDEEP
sqldateformatms-access-2010case
francis sextan
francis sextan

Reputation: 3

SQL / How to sort values by their format

I need to recognize which of several formats a date is in. Example table:

the table i need to work on

On the screenshot (this is an example, but on reality there is 120K+ rows), 'DATEPRO' values can have three different styles :

I would like to know if there is more than one year of difference between 'DATEPRO' and 'DATECON'.

If DATE PRO is "FY"+YY/YY style, I would consider only the 2 last digits (YY) as a year

If DATE PRO is "In 3m" style it would be No for obvious reasons

If DATE PRO is "YYYY" style, it will be an easy substraction

examples :

I think I should use the CASE operator, but it seems to be only applicable to specific cases, and not to detect the format of a column. My real problem is not the substraction part of the operation, but the recognition of the format.

Upvotes: 0

Views: 53

Answers (2)

Gustav
Gustav

Reputation: 55961

You can use:

Select 
    *,
    Not (Right(DatePro, 2) = Format(DateCon, "yy") Or Right(DatePro, 2) = "3m") As DateMatch
From
    YourTable

Upvotes: 0

Rominus
Rominus

Reputation: 1221

This answer depends on your data containing only those formats you specify in your question, and that all years are at least 2000, which must be true if you're depending on the last two digits for FY formatted values.

You're on the right tract with CASE and you can use it to identify formats in a situation like this where you know what limited set of formats you have. You can then use CASE to convert the mis-formatted values into what you need. Like this:

select 
case 
    when left(DATEPRO,2) = 'FY' then concat('20', right(DATEPRO,2))
    when DATEPRO like '2___' then DATEPRO
    else null
    end
as DATEFIX
from your_table

The first check triggers if the first two characters are "FY". The second check triggers if the value starts with "2" and is exactly four characters long. The else null weeds out everything else.

However, your tags indicate you're working with MS Access, which doesn't support CASE. Instead it entirely replaces it with IIF, which functions as a simplified CASE. IIF only supports a statement with one if/else condition so you have to write nested IFF statements for something like this. It's much harder to write and harder to read. The equivalent code for an Access query would be:

select 
IIF(
    left(DATEPRO,2) = 'FY'
    ,'20' & right(DATEPRO,2)
    ,IIF(
        DATEPRO like '2###',
        DATEPRO,
        null)
    )
as DATEFIX
from your_table

Upvotes: 1

Related Questions