You can translate the question and the replies:

Subtracting a no from a date DATEDIFF(dd,0,b.pymt_run)

Can you please help me to convert following statement in denodo DATEADD(dd, CASE WHEN DATEDIFF(dd,0,b.pymt_run)%7 > 4 THEN 7-DATEDIFF(dd,0,b.pymt_run)%7 ELSE 0 END,b.pymt_run) as fin_pymt_run Here b.pymt_run is of date datatype. Thank you!
user
12-11-2019 22:20:59 -0500

3 Answers

Hi, The function you are looking for is called GETDAYSBETWEEN. You can find more information on the documentation about [Datetime Processing Functions](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/appendix/syntax_of_condition_functions/date_processing_functions). I would also refer to the [CASE clause](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/queries_select_statement/case_clause/case_clause) documentation as I see you are using that too. I believe what you are using is SQL Server. If that is the case, please take a look at this article of our knowledge base: [Microsoft SQL Server SQL to Denodo VQL Quick Reference](https://community.denodo.com/kb/view/document/Microsoft%20SQL%20Server%20SQL%20to%20Denodo%20VQL%20Quick%20Reference?category=VQL) where you can find more conversions between SQL Server and Denodo VQL. Hope this helps!
Denodo Team
13-11-2019 14:45:47 -0500
I have used getdaysbetween function, however following function still doesn't work : getdaysbetween(cast('1900-01-01' as date),pymt_run). Here pymt_run is of datatype date.
user
13-11-2019 17:52:39 -0500
Hi, I have used that same statement on my platform with a date data type field (although it is deprecated and recommended to use the new [Data Types for Dates, Timestamps and Intervals](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/language_for_defining_and_processing_data_vql/data_types/data_types_for_dates_timestamps_and_intervals)) and it works perfectly fine. On a different note, to convert a String into a date type it is better practice to use the specific functions to convert to this types such as [TO_LOCALDATE](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/appendix/syntax_of_condition_functions/date_processing_functions#to-localdate) Please note that this statement has to be included in a Select statement as the documentation points out. I would review that the quotes are good and that it follows the examples on the documentation. Hope this helps!
Denodo Team
21-11-2019 16:05:22 -0500
You must sign in to add an answer. If you do not have an account, you can register here