Best Practices for query on DataTime Fields

Posts   
 
    
Luca75
User
Posts: 37
Joined: 10-Feb-2012
# Posted on: 26-Sep-2017 12:12:58   

Hi guys I would need advice on how to do this typology of queries are on MsMsql 2015 Sample data table:

|TimestampDate | Value|

01/01/2017 00:01:00.000 | 1 01/01/2017 00:02:00.000 | 2 02/01/2017 00:01:00.000 | 3 02/01/2017 00:02:00.000 | 4 03/01/2017 00:01:00.000 | 5 03/01/2017 00:02:00.000 | 6

SELECT [TimestampDate], [Value] FROM Sample WHERE Sample.[TimestampDate] BETWEEN '01 / 01 / 2017' AND '02 / 01 / 2017' AND DATEPART(HOUR,[TimestampDate])=1

Result: 01/01/2017 00:01:00.000 | 1 02/01/2017 00:01:00.000 | 3

SELECT [TimestampDate], [Value] FROM Sample WHERE Sample.[TimestampDate] BETWEEN '01 / 01 / 2017' AND '02 / 01 / 2017' AND DATEPART(HOUR,[TimestampDate]) IN(1,2)

Result: 01/01/2017 00:01:00.000 | 1 01/01/2017 00:02:00.000 | 2 02/01/2017 00:01:00.000 | 3 02/01/2017 00:02:00.000 | 4

I love using LLBLGen native Api Thanks

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 27-Sep-2017 07:24:50   

Could you please be more specific on you question?

As this is a basic filtering, please mention which part you need help with? Is it the DATEPART db function?

If yes, then please check Calling a Database Function

Luca75
User
Posts: 37
Joined: 10-Feb-2012
# Posted on: 27-Sep-2017 07:38:25   

Yes datepart in LowLevel LLBLGen API Thanks

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 27-Sep-2017 20:38:46   

Here you are (example on Northwind filtering on the month) if you haven't figure it out from the docs:

var field = OrderFields.OrderDate.SetExpression(new DbFunctionCall("DATEPART(MONTH,{0})", new object[] { OrderFields.OrderDate })); var filter = new PredicateExpression(field.Between(9, 10));

Luca75
User
Posts: 37
Joined: 10-Feb-2012
# Posted on: 27-Sep-2017 23:28:40   

Wonderful thank you! But is there documentation with examples on Nortwind database?

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 28-Sep-2017 07:24:58   

Luca75 wrote:

But is there documentation with examples on Nortwind database?

The examples in the link Walaa posted are for Northwind, also the example he wrote down is for Northwind.

What are you looking for exactly? Could you give a try with your own code and then give us feedback on it.

David Elizondo | LLBLGen Support Team
Luca75
User
Posts: 37
Joined: 10-Feb-2012
# Posted on: 28-Sep-2017 07:43:07   

Hi, thank you and I will let you know. I thought there was documentation with interesting query examples on Nortwind if there was one suggestion ... Thank you

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 28-Sep-2017 09:26:15   

In the 'Frameworks\LLBLGen Pro' folder in the designer installation folder you'll find example queries on e.g. northwind, adventureworks etc. They're not very well structured tho, which is something we need to spend some time on.

Frans Bouma | Lead developer LLBLGen Pro