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 ...
DECLARE @srchDateBegin NVARCHAR(30) SET @srchDateBegin = '13/04/2022' SELECT CONVERT(DATE, @srchDateBegin, 105)
Answered By – Nayanish Damania
Answer Checked By – Terry (BugsFixing Volunteer)