DateDiff

Kinetic ERP
1

Hello!

What is the formula if I want to take the difference in days between an invoice due date and the date I entered in my parameter?

I would’ve thought it would be:
DateDiff (Day,InvcHead.DueDate, @Date)
But the syntax says “Common table expression defined but not used.”

Thanks!

2

Not sure how case sensitive that is, I always use

DATEDIFF(day,

1 Like
3

This is the way

SQL Server DATEDIFF() Function

1 Like
4

Make sure the parameter’s defined as a date format

Suggest renaming the parameter to be @XDueDate or something like that…wonder if the BAQ’s getting squirrely in translating @Date

EDIT - or ignore me and follow @Randy lol

5

@JMPCONJ is correct a query can’t use SQL Reserved Words. I believe that “Date” is one. Good catch John!

6

May need to print that on a T-shirt for Insights… :thinking:

2 Likes
7

So it’s the sequence of the DateDiff command AND the parameter name.

Reminds me of the SNL bit…it’s a floor wax AND a dessert topping

image

2 Likes
8

I think “In Hannah we trust” would make a better t-shirt and be more accurate.

2 Likes
9

I think two people standing side-by-side with those shirts would be pretty funny.

“In Hannah we trust… or, ignore me and follow Randy.”

Though, it changes the connotation to make it sound like following Randy is the poor choice, haha.

Kinda like:
“You shouldn’t step out into traffic… but, hey, do what you want.”

1 Like
10

That’d probably be the safer assumption. :rofl: :rofl:

11

Okay - it appears this is the correct way and I just had a different error. Thanks all!

2 Likes
12

Technically, @Date isn’t the same as Date, the latter is (as you stated) reserved and will cause issues unless enclosed in brackets (in SQL). BAQ’s will usually handle that, but it’s a bad practice. You can go wild on @Variable names.

1 Like
13

I usually just stick parm* in front of whatever field I’m querying against:
parmOrderNo, parmCustID, parmPartNum

-* only because a prefix of “chickenparm” or “vealparm” or “meatballparm” as the prefix seems a little too verbose

1 Like
14

Hmm, I read that as Chicken Parmesan :rofl:

15

I withhold judgement on @ChickenParm until I review the T-SQL naming conventions again.

16

image

2 Likes
17

But the real question is it Chicken Parma or Chicken Parmi… or Chicken Parmy?

As long as it good quality ham and lots of cheese, and followed closely with a good cold beer. That’s all that should matter.

Talking of dateadd. I learnt today trying to put DateTime.Today.AddDays(-2) into a linq statement in a function does not work, compiles but errors on execution.

2 Likes
18

There must be some other issue. That’s a valid C# expression.
Only think I can think is that there are limitations in LINQ to SQL (IQueryable) statements.

1 Like
19

when I looked it up I ended up having to create the variable i.e.

var TwoDaysHence = DateTime.Today.AddDays(-2);

and then put TwoDaysHence into the linq statement.

worked after that…

1 Like