SQL query accepting date parameter in MM-DD-YYYY format instead of DD-MM-YYYY-CodePudding

Here is my stored procedure which accepts two date parameters, one for start date and second for end date. It gets a bunch of different data from joined tables:

@FromDate varchar(50),
@ToDate varchar (50)

SELECT DISTINCT 
    dbo.DefectInspection.DefectInspection_Id, 
    CONVERT(varchar(50), CAST(dbo.DefectInspection.DefectInspection_CreatedDate AS date), 34) AS CreatedDate
FROM           
    (bunch of tables)
WHERE
    CAST(DefectInspection.DefectInspection_CreatedDate AS date) 
        BETWEEN CAST( @FromDate AS Date) AND CAST(@ToDate AS Date)

The issue is it will only return date if I input my dates as MM-DD-YYYY instead of the days firsts. This is an issue because the date style sent from client side is always DD-MM-YYYY

Using desired input: no data returedn

Table W/o data

Using month format - data returned

Table With Date

CodePudding user response：

When using SQL (and other languages) its best to use an unambiguous format such as yyyy-mm-dd

Also consider using the DATE type instead of VARCHAR

@FromDate DATE(50),
@ToDate DATE(50)

CodePudding user response：

Though using the wrong datatypes is terrible, sometimes we all have to deal with issues which cannot be changed easily.

You can use:

DECLARE @fromDate AS varchar(50);
DECLARE @toDate AS varchar(50);
DECLARE @from AS date;
DECLARE @until AS date;

SET @fromDate = '09-01-2023';
SET @toDate = '10-01-2023';

SET @from = TRY_CONVERT(date, @fromDate, 105);
SET @until = TRY_CONVERT(date, @toDate, 105);

IF @from IS NULL OR @until IS NULL
    THROW 51000, 'Parameter is not a valid date ..', 0;
ELSE BEGIN
    SELECT DISTINCT 
        ...
    FROM           
        (bunch of tables)
    WHERE TRY_CONVERT(date, DefectInspection.DefectInspection_CreatedDate, 110) BETWEEN @from AND @until;
END;

TRY_CONVERT returns NULL if the parsing fails. The 3rd parameter (style) 105 means format "dd-mm-yyyy" like you use it, 110 means "mm-dd-yyyy".