Convert VARCHAR in French/France Date format to Date-CodePudding

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 @style INT
DECLARE @recordDate DATE
DECLARE @srchDateBegin NVARCHAR(30)
DECLARE @localeID NVARCHAR(10)
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:


-SQL SELECT STUFF-
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 ...

CodePudding user response：

Based on what you've written, you're actually converting something into NVARCHAR and not the other way around.

Syntax for CONVERT and CAST:

-- CONVERT syntax:
CONVERT(data_type(length), expression, style)

-- CAST syntax:
CAST(expression AS data_type(length))

Note: Only data_type and expression are required; length and style are optional.

Suggestion:

-- your code:
CONVERT(NVARCHAR(30), CAST(@srchDateBegin AS DATE), @style)

-- suggestion:
CONVERT(date, @srchDateBegin, @style)

Sources:

Quick info from W3Schools.
Comparing CAST and CONVERT from Microsoft's documentation.

CodePudding user response：

Try this:

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

SELECT CONVERT(DATE, @srchDateBegin, 105)