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.

Result Set


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:


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

      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.

  • Hi, Joe! Yes, I wish I can change that column to date. But they are using that before I came. Thanks so much for your help. My regularization depends on this project so it is a great help! Thankyou again :) – Dan Angelo Alcanar Feb 5 '18 at 4:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.