Check if date is between two dates in SQL
Application Type
Mobile, Reactive

Hi Community,


At the moment I am developing an application where the user can choose a From and a To date and if a certain database field is between those two dates data must be shown. I've made the where statement (see screenshot below) but when executing I am getting a error named "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. " 

"WHERE {ENTITY}.[ENTITYATTRIBUTE] BETWEEN '" + FromDate + "' AND  '" + ToDate + "'"

In Aggregate I would normally do something like this:

Entity.EntityAttribute >= FromDate and Entity.EntityAttribute < AddDays(ToDate, 1)

Good to know: Local variables (From and To) have the Date Data Type and also the entity attribute has the Date Data Type.

Not sure why it is telling something about datetime since none of the used attributes has datetime as data type. Anyone knows how to solve this issue? If more information is needed please let me know.

Best,

Bart

Hi Bart,

the reason it isn't working the way you did it, is the date literal you constructed in your dynamic where clause is not a valid date literal for SQL Server.

In SQL Server (and maybe also in Oracle, not sure), a date literal has to be in format 'YYYY-MM-DD'.

You were building your dynamic where clause by concatenating Text with a date variable, and it is perfectly possible to do that, the OS platform will convert your date to a string representation under water.  The only problem is, that this will be in the format as has been set up in your Environment configuration.

For example, in my personal environment, that is currently :

SQL server doesn't recognise this as a valid date literal, and will throw that error.

So, EITHER you change your date format in the configuration (YYYY-MM-DD is one of the options), but that has an impact on all your applications, OR do the formatting yourself instead of letting OS do it under water.  So you'll have to do something like :

FormatDateTime(FromDate,"yyyy-MM-dd")

Dorine

Hi Dorine,

Thanks for the explanation as why this is not working as I expected. Will try your last suggestion as the first option can affect other applications. When it works I will mark your answer as the solution.

For others that encounter the same problem and are looking for the solution check the code below as this made it work for me.

"WHERE {ENTITY}.[ENTITYATTRIBUTE] BETWEEN '" + FormatDateTime(FromDate, "yyyy-MM-dd") + "' AND '" + FormatDateTime(ToDate, "yyyy-MM-dd") + "'"

Hi Bart,

You need to convert your date to SQL statement: to_date(<date>, <format_date>)

Then you can pass to your statement in input parameter with Expand Inline = True.


Best Regards,

Hi Eduardo,

Thanks for your reply. Pretty new to SQL but it sounds like something that could work. Will try it. Thanks!

Hi Bart,

Can you please try this one?

WHERE {ENTITY}.[ENTITYATTRIBUTE] BETWEEN @FromDate AND @ToDate

I hope it works for you.


Regards,

Necmettin

Hi Necmettin,

Thanks for your reply. I would do it like that if I made the SQL directly in the SQL node but I am dynamicly building my WHERE statement with expressions. I guess I have to do something with to_date as Eduardo mentioned.

Hi Bart,

the reason it isn't working the way you did it, is the date literal you constructed in your dynamic where clause is not a valid date literal for SQL Server.

In SQL Server (and maybe also in Oracle, not sure), a date literal has to be in format 'YYYY-MM-DD'.

You were building your dynamic where clause by concatenating Text with a date variable, and it is perfectly possible to do that, the OS platform will convert your date to a string representation under water.  The only problem is, that this will be in the format as has been set up in your Environment configuration.

For example, in my personal environment, that is currently :

SQL server doesn't recognise this as a valid date literal, and will throw that error.

So, EITHER you change your date format in the configuration (YYYY-MM-DD is one of the options), but that has an impact on all your applications, OR do the formatting yourself instead of letting OS do it under water.  So you'll have to do something like :

FormatDateTime(FromDate,"yyyy-MM-dd")

Dorine

Hi Dorine,

Thanks for the explanation as why this is not working as I expected. Will try your last suggestion as the first option can affect other applications. When it works I will mark your answer as the solution.

For others that encounter the same problem and are looking for the solution check the code below as this made it work for me.

"WHERE {ENTITY}.[ENTITYATTRIBUTE] BETWEEN '" + FormatDateTime(FromDate, "yyyy-MM-dd") + "' AND '" + FormatDateTime(ToDate, "yyyy-MM-dd") + "'"

Hey buddy,
you may user convertof your date to respective formate and then compare with date inSQL
your query like

"WHERE {ENTITY}.[ENTITYATTRIBUTE] BETWEEN '" + convert(datetime,FromDate,103) + "' AND  '" + convert(datetime,ToDate,103) + "'"
use datetime in convert if you usage datetime as data type of your column else use date.
Some conversion format are given in below link.
https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/
please mark solution if it helps you.

Hi Yogesh,

That won´ t work.  He is building the entire where clause before executing the query, sql server functions are not available at that point.

Also, if it were, 103 doesn´ t seem the right choice.

Dorine 

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.