[SOLVED] Convert VARCHAR in French/France Date format to Date


I’m having some issues with date conversions and have tried a lot of the previous posts. However, most of the previous posts seem to tackle converting English Dates to other Cultures and vice versa.

Here’s the problem:

I have a date in ‘String’ format that get’s passed into a variable. I want to cast that into a date WITHOUT changing the language. I want to do this so that I can look through records between dates.

DECLARE @recordDate DATE
DECLARE @srchDateBegin NVARCHAR(30)
SET @localeID = '1036'
SET @srchDateBegin = '13/04/2022'
SELECT @style = CASE WHEN @localeID = '1036' THEN 103 ELSE 101  END

This is all the declaration stuff (the structure is a bit different but I’ve written it out this way to make it a bit simpler. Below is one way I’ve tried converting everything but that didn’t work:

AND (@srchDateBegin = ''
    OR @recordDate BETWEEN CONVERT(NVARCHAR(30), CAST(@srchDateBegin AS DATE), @style) AND ...

I then tried to use the FORMAT function in SQL but it doesn’t want NVARCHARs but because I can not cast an NVARCHAR format of ’16/02/1996′ into a DATE, I’m stuck

AND (@srchDateBegin = ''
OR @recordDate BETWEEN
   CASE WHEN @style = '103' AND '@srchDateBegin' <> '' THEN FORMAT(@srchDateBegin, 'dd/MM/yyyy') ELSE '@srchDateBegin' END AND ...


Try this:

DECLARE @srchDateBegin NVARCHAR(30)
SET @srchDateBegin = '13/04/2022'

SELECT CONVERT(DATE, @srchDateBegin, 105)

Answered By – Nayanish Damania

Answer Checked By – Terry (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.