Compare datetime variables in stored procedure

Question

0.00/5 (No votes)

See more:

I am trying to compare two datetime variables in a stored procedure sql server.
In the below code snippet @createdDate is taken as user input and then comparing with a column of type datetime. I am unable to check the ''='' property

set @sqlquery = 'Select
            v.*,
            vsc.vidhanSabhaConstituencyName, where 1=1 '

set @sqlquery = @sqlquery +'AND v.createdBy ='''+cast(@createdBy as nvarchar(100))+''''

if(@VoterIdNumber is not null)
set @sqlquery = @sqlquery+'AND v.voterIDNumber= '''+@VoterIdNumber+''''

if(@createdDate is not null)
set @sqlquery = @sqlquery+'AND v.dataIsCreated = '''+cast(@createdDate as varchar(100))+''''
else
set @sqlquery = @sqlquery+'AND v.dataIsCreated= '''+cast(getdate() as varchar(100))+'''' 

Execute sp_Executesql @sqlquery

What I have tried:

I've tried casting and converting the @createdDate variable without success.
It works with other operators like >= or <= but not with = .

Help is appreciated

Posted 15-Mar-18 3:44am

Abhi1 Kanobi

Updated 15-Mar-18 8:00am

v2

Add a Solution

Comments

F-ES Sitecore 15-Mar-18 9:54am

Look at what is in "@createdDate", look at what is in dataIsCreated, look at what is returned by GetDate(). Are they the same. Literally the same? If not that is why your "=" is failing. You are making assumptions about things so the solution is to work out what your failing assumption is and how to remove that assumption. eg are you assuming that 2018-03-15 is the same thing as GetDate()? If you are then that assumption is wrong as GetDate includes the time so 2018-03-15 does NOT equal 2018-13-15 13:55. That's just one of many assumptions you might be making, we can't access your data so can't say for sure.

Also don't convert dates to strings, that's only going to give you even more problems on top.

Abhi1 Kanobi 15-Mar-18 10:01am

Actually , the problem is, I am making a dynamic query based values supplied by the user . @sqlquery in the problem is a string that is executed. All my columns are datetime .

when I am not casting and comparing the @createdDate directly , it gives error as mentioned below "The data types nvarchar and date are incompatible in the add operator."

2 solutions

Solution 1

Why are you trying to use string based dates?
If your dataIsCreated column is VARCHAR or NVARCHAR, then that's a mistake: make it a DATE, DATETIME, or DATETIME2 value instead, and compare DATE or DATETIME values.

The problem is that string based dates depend on the locale setting for the machine that inserted them, and you have little control over that. And dates can be entered in three main formats: yyyy/MM/dd, MM/dd/yyyy, and dd/MM/yyyy - so when you cast your date to a string you have absolutely no real control over what you are comparing to what!

Store and compare dates as dates - if you dont; you will give yourself a huge numebr of problems.

Posted 15-Mar-18 3:54am

OriginalGriff

Comments

Abhi1 Kanobi 15-Mar-18 10:00am

Actually , the problem is, I am making a dynamic query based values supplied by the user . @sqlquery in the problem is a string that is executed. All my columns are datetime .

when I am not casting and comparing the @createdDate directly , it gives error as mentioned below "The data types nvarchar and date are incompatible in the add operator."

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2018-03-15T08:01:00

Don't do it like that!

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

In SQL, that means using sp_executesql[^] properly:

SQL

DECLARE @sqlquery nvarchar(2000);

SET @sqlquery = N'SELECT v.*, vsc.vidhanSabhaConstituencyName
FROM SomeTable As v
INNER JOIN SomeOtherTable As vsv ON <join conditions>
WHERE 1 = 1';

SET @sqlquery = @sqlquery + N' AND v.createdBy = @createdBy';

If @VoterIdNumber Is Not Null SET @sqlquery = @sqlquery + N' AND v.voterIDNumber = @VoterIDNumber';

If @createdDate Is Null SET @createdDate = GetDate();
SET @sqlquery = @sqlquery + N' AND v.dataIsCreated = @createdDate';

EXEC sp_executesql
    @sqlquery,
    N'@createdBy varchar(50), @VoterIDNumber varchar(50), @createdDate date', -- TODO: Use the correct types here
    @createdBy = @createdBy,
    @VoterIDNumber = @VoterIDNumber,
    @createdDate = @createdDate
;

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]