Advanced sql date comparison

HI,

I have an advanced SQL query to use for filtering.

The first part of the query however causes an error.

The error says: Invalid cast from DateTIme to Int32.

Logs tells me it cannot convert 1/22/2019 to a date.

However, the value in the database is: 2019-01-22 00:00:00

It occurs for the following part:


SELECT * FROM {GLAccount_Reconciliations} WHERE
@StartDate  >= {GLAccount_Reconciliations}.[ChangedAt] AND
@EndDate   <= {GLAccount_Reconciliations}.[ChangedAt]


If I change the bottom line comparison to >= then the test runs without errors and the result is no rows.

StartDate and EndDate are in DateTime formats.

I have also tried CONVERT and other methods to translate date to INT. All of them give me the same error.

First off, try to get a date in the format of YYYY-MM-DD.
Once you've done that, you use the following convert:

Convert(DateTime, convert(VARCHAR(30), 'YYYY-MM-DD'),103)

Make sure to replace the YYYY_MM-DD with the variable that holds your date.

Basicaly, we tell SQL that our date is actually a string/varchar and that we want to convert it to a datetime.

Convert(DateTime, convert(VARCHAR(30), 'YYYY-MM-DD'),103) >= {GLAccount_Reconcillations}.[ChangedAt]

Hi Johnny,


Have you tried with SELECT {GLAccount_Reconciliations}.* instead of SELECT *.

In any case you should be able to perform this query in an aggregate?


Cheers,

João

Hi Johnny B Good,

Can once again check the data type of ChangedAt, StartDate, and EndDate and tell me what is the data type of it?Is It Same?

I have a doubt about StartDate and EndDate data type.


Regards,

Amreen





Joey Moree wrote:

First off, try to get a date in the format of YYYY-MM-DD.
Once you've done that, you use the following convert:

Convert(DateTime, convert(VARCHAR(30), 'YYYY-MM-DD'),103)

Make sure to replace the YYYY_MM-DD with the variable that holds your date.

Basicaly, we tell SQL that our date is actually a string/varchar and that we want to convert it to a datetime.

Convert(DateTime, convert(VARCHAR(30), 'YYYY-MM-DD'),103) >= {GLAccount_Reconcillations}.[ChangedAt]

The problem is not with the startdate and enddate. It is with the date that is already in the database.. the changedAt


Amreen Shaikh wrote:

Hi Johnny B Good,

Can once again check the data type of ChangedAt, StartDate, and EndDate and tell me what is the data type of it?Is It Same?

I have a doubt about StartDate and EndDate data type.


Regards,

Amreen






All of them are DateTime. I have run the test with test values of dateTime (#2019-02-07 00:00:00#)

João Marques wrote:

Hi Johnny,


Have you tried with SELECT {GLAccount_Reconciliations}.* instead of SELECT *.

In any case you should be able to perform this query in an aggregate?


Cheers,

João

Running the query not in advanced sql but in aggregate works fine. However that is not what I am trying to do here.


Ah my bad Johhny, I have a generic sql creator tool running on Outsystems, I have a few tricks in there where I also needed this. 

This is the direct query that is executed for me:

{MyEntity}.[CreationDate] BETWEEN Convert(DateTime, convert(VARCHAR(30), '03-02-2019'),103) AND Convert(DateTime, convert(VARCHAR(30), '22-02-2019'),103)

Please note the use of between (it's better to use between than 2 seperate statements).
And the fact that the date I'm using is encapsulated in single quotes ('). I've been having the same problems as you, weird convert errors, but this is working for me.


Though.... I'm running on .NET stack with MSSQL, you might experience some differences if you are running JAVA stack with MySQL.

Joey Moree wrote:

Ah my bad Johhny, I have a generic sql creator tool running on Outsystems, I have a few tricks in there where I also needed this. 

This is the direct query that is executed for me:

{MyEntity}.[CreationDate] BETWEEN Convert(DateTime, convert(VARCHAR(30), '03-02-2019'),103) AND Convert(DateTime, convert(VARCHAR(30), '22-02-2019'),103)

Please note the use of between (it's better to use between than 2 seperate statements).
And the fact that the date I'm using is encapsulated in single quotes ('). I've been having the same problems as you, weird convert errors, but this is working for me.


Though.... I'm running on .NET stack with MSSQL, you might experience some differences if you are running JAVA stack with MySQL.

Your code works when I use literal dates ('03-02-2019') , however when I try to use the values from start and enddate (type text: '15-01-2019' and '07-02-2019') I get the same cast error...


SELECT * FROM {GLAccount_Reconciliations} WHERE 
{GLAccount_Reconciliations}.[ChangedAt] BETWEEN Convert(DateTime, convert(VARCHAR(30), @StartDate ),103) AND Convert(DateTime, convert(VARCHAR(30), @EndDate ),103)


why do a convert from datetime, to varchar to datetime ?? the convert(varchar(30, @enddate) will result in a dateformat based on your (sql) server dateformat. Then back to datetime using 103 can result in the wrong dateformat assumption. But when your params are already datetime its not nessecary to do the funny conversions .. 

By the way, it's always best to use the YYYYMMDD format ... then sql always assumes the right sequence, when using YYYY-MM-DD sql can also assume the month and day interchanged. 

Are you really sure the ChangedAt is a datetime field ? If its a timestamp it's not a datetime field .. then it's a longinteger and thus the convert error because then the 2 dates are converted to integer and that's not possible


By the way, the between is converted inside sql intrepreter as >= and <= so it's really the same.


So ChangedAt between @startdate and @enddate is the same as ChangedAt >= @startdate and ChangedAt <= @EndDate

By The way ... 

The query 

SELECT * FROM {GLAccount_Reconciliations} WHERE
@StartDate  >= {GLAccount_Reconciliations}.[ChangedAt] AND
@EndDate   <= {GLAccount_Reconciliations}.[ChangedAt]

Is wrong I think .. if you want all recons between a period it should be like : 

SELECT * FROM {GLAccount_Reconciliations} WHERE
{GLAccount_Reconciliations}.[ChangedAt] >= @StartDate AND
{GLAccount_Reconciliations}.[ChangedAt] <= @EndDate  

For readablillity (and preventing errors) its better to use the between 

SELECT * FROM {GLAccount_Reconciliations} WHERE
{GLAccount_Reconciliations}.[ChangedAt] BETWEEN  @StartDate AND @EndDate  

Further more, if you did a number of changes to your GLAccount_Reconciliations entity the order of the fields can differ, using the select * can be a problem, you have to map the outcome to a struct you define (problably you use the entity struct as the output struct) but if for instance you deleted fields this wont work and can result in a conversion error.. select * will result in all the fields in the table, i think using select {GLAccount_Reconciliations}.* will hopefully result in only the fields in the table which match the entity .. otherwise to be real sure you use all the field names and ignore the outsystems warning you should use the select * :-)


regards,

Wim

Wim van den Brink wrote:

By The way ... 

The query 

SELECT * FROM {GLAccount_Reconciliations} WHERE
@StartDate  >= {GLAccount_Reconciliations}.[ChangedAt] AND
@EndDate   <= {GLAccount_Reconciliations}.[ChangedAt]

Is wrong I think .. if you want all recons between a period it should be like : 

SELECT * FROM {GLAccount_Reconciliations} WHERE
{GLAccount_Reconciliations}.[ChangedAt] >= @StartDate AND
{GLAccount_Reconciliations}.[ChangedAt] <= @EndDate  

For readablillity (and preventing errors) its better to use the between 

SELECT * FROM {GLAccount_Reconciliations} WHERE
{GLAccount_Reconciliations}.[ChangedAt] BETWEEN  @StartDate AND @EndDate  

Further more, if you did a number of changes to your GLAccount_Reconciliations entity the order of the fields can differ, using the select * can be a problem, you have to map the outcome to a struct you define (problably you use the entity struct as the output struct) but if for instance you deleted fields this wont work and can result in a conversion error.. select * will result in all the fields in the table, i think using select {GLAccount_Reconciliations}.* will hopefully result in only the fields in the table which match the entity .. otherwise to be real sure you use all the field names and ignore the outsystems warning you should use the select * :-)


regards,

Wim

The comparison should indeed be other way around, but that is not the point. It would simply return no results if run with the comparison I gave.


Solution

Well, the solution was as follows ( I only needed to change * to: {GLAccount_Reconcilitaions).* )

SELECT {GLAccount_Reconciliations}.* FROM {GLAccount_Reconciliations} WHERE
[ChangedAt] BETWEEN  @StartDate AND @EndDate 
Solution

Johnny B Good wrote:

Well, the solution was as follows ( I only needed to change * to: {GLAccount_Reconcilitaions).* )

SELECT {GLAccount_Reconciliations}.* FROM {GLAccount_Reconciliations} WHERE
[ChangedAt] BETWEEN  @StartDate AND @EndDate 

I am glad my proposed solution worked ;-)