I created a Pass Thru Variable for a date to enter at run time.

I need the SQL syntax to create the 2 digit month from Pass Thru Variable to use as a comparison.

The date comes out as "26 December 2017"

Any ideas?

PS - This is in a Providex database.

Views: 259

Reply to This

Replies to This Discussion

While I'm asking what would the syntax be to pull the 4 digit Year?

Bump

Hi Doug.

I'll take a look at this and see if I can come up with anything.

I appreciate it.

Hi Doug.

I tried this and could get it to work using the MONTH function with the UQE turned on.  In my query however the date for my passthrough variable came through in the following format: {d '2018-10-23'}.  So I'm wondering if there's anything to do with the setup/variable type of your passthrough that's bringing your date through in the format it is.

Peter - Thanks for checking on this.

My aim was to just get the 2 digit month. For example with the Date 2018-10-23 I want to pull "10".

Also looking to just pull the Year i.e. "2018"

PS - Are you testing it with Sage 100 Providex?

Yes, it was 100 Advanced.  I also checked and the YEAR function works for the year as well.

Peter - I posted this last year and couldn't find the report that I had the issue with the way the Pass Through displayed the date. I just tested it and it does come thru as yours "YYYY-MM-DD".

However I still have an issue trying to get the Month from the Pass Though in my Container.

Here is the setup for the Pass Through:

This is what I want to do with the Where clause in the Container:

I get errors testing.

How can I set it up correctly?

Doug in the example above what is the error? If possible I would change the datatype on the PassThrough to date not varchar if possible. I believe the function month() and year() work with date data types. It seems that the month function see the passthrough variable as a string. 

Yes the YEAR scalar function is a way of doing this. 

Here is can example. select top 10 customerno, {fn YEAR(DateCreated)} from AR_Customer. 

Here is an example with both month and year in the same statement. 

select top 10 customerno, {fn Month(DateCreated)} AS Month, {fn YEAR(DateCreated)} as YEAR from AR_Customer

Not sure what tools you use to test SQL commands with the ProvideX driver WinSQL is a nice tool.

Timothy - I'm relying here because I can't reply to the other one.

I changed the Pass Through to "Date" and still get the error:

Here is a screen shot from the SQL Debugger showing how the Where comes out and the error when I ran the test:

Here a copy of the report if your bored and what to play with it.

Attachments:

RSS

The Sage Intelligence Blog

Like Sage Intelligence?

Follow @SageSupport

© 2019   Created by Sage Alchemex.   Powered by

Badges  |  Report an Issue  |  Terms of Service