MS SQL - Convert string mm/dd to date

Question

I am having a bit of a hardtime in converting string to date in MS SQL. Hope you can guide me.

Here is my scenario:

I have a column in my table called ExpDt. It is the expiration date of the product. the format is mm/dd.

Now I want to know what products would expire 5 days before. So I am going to do a datediff. But the problem is the ExpDt column is not a valid date. How can I convert it to a valid date?

So the output would be something like: 2018-01-20 which is a valid date. Not a string.

Here's what I have tried.

-- Outputs 2018-01-31
-- select  @expDate = cast(datepart(year, getdate()) as nvarchar(5)) 
-- + '-' + substring(ExpDt, 1, 2) 
-- + '-' + substring(ExpDt, 4, 5)

-- from BigEMerchandiser.dbo.tbl_Abis_D

It returns a string but I can't convert it to date using the convert function. I will attach the screenshot of the table so you can see the data of the ExpDt column. Hope I explained myself clearly. Any help would be much appreciated. Thankyou.

Answer 1

The best thing that you can do is fix your table so that you store a date instead of a string. With that said, the code that you posted in the question is on the right track. The problem is that you leave it as a string and don't explicitly cast it to a DATETIME. By the rules of data type precedence the data that you filter against also gets converted to a string which isn't what you want.

If you want to keep the SUBSTRING approach then you can try the following:

CREATE TABLE #T (NOT_A_DATE NVARCHAR(5));

INSERT INTO #T VALUES (N'01/30'), (N'01/31');

SELECT
    DATEFROMPARTS(
      datepart(year, getdate())
    , substring(NOT_A_DATE, 1, 2) 
    , substring(NOT_A_DATE, 4, 5)
)
FROM #t;

The key is the use of the DATEFROMPARTS function to convert the string to a date that's safe in all locales.