9 Replies Latest reply: Dec 23, 2009 10:57 PM by John Witherspoon RSS

    Help with dates.

      Hello all!

      Well, I'm pretty new to Qv, so go easy on me.

      I'm having some issues understanding the way that Qv handles dates... what I'm trying to do is select records where the date is equal to the current month on my system. The data is in YYYY/MM.... I was trying to do GETDATE() with MID functions to extract the 'year'+/+'mo'. But to no avail.


      Any suggestions?



        • Help with dates.

          Use TODAY() to get date, and SUBFIELD instead of MID.

          Good luck.

            • Re:Help with dates.

              What format is the TODAY() output in?

                • Re:Help with dates.
                  Community Administrator

                  Can you post a small snippet of your data? I'm not quite sure what you're asking... thanks!

                  Also, check out the QlikView help, search for "Date and Time Functions", tons of great info there.

                    • Re:Help with dates.

                      When I run the report I need to see totals from the current month, as well as the previous 3 months. Like I said, my data is stored as YYYY/MM (2009/12, 2009/11, 2009/10....)

                      When I enter...

                      SQL SELECT * FROM blahblah

                      WHERE YearMonth='2009/12'

                      ...I get the correct output. But I want to change the WHERE clause to be a variable that automatically changes the output from month-to-month.

                        • Re:Help with dates.

                          should it look like this?

                          WHERE YearMonth='year(left(YearMonth,4))'+'/'+'month(right(YearMonth,2))'

                          Also, do I have to convert to int before I can subtract for previous months?


                          • Re:Help with dates.
                            Rob Wunderlich

                            If you want to set the SQL WHERE clause to the current month, in your specified format, you can do it like this:

                            LET currentMonth = date(today(), 'YYYY/MM');

                            SQL SELECT * FROM blahblah
                            WHERE YearMonth='$(currentMonth)'

                            And multiple months like this:

                            LET currentMonth = date(today(), 'YYYY/MM');
                            LET month1 = date(AddMonths(today(),-1), 'YYYY/MM');

                            SQL SELECT * FROM blahblah
                            WHERE YearMonth IN ('$(currentMonth)', '$(month1)')


                            • Re:Help with dates.
                              John Witherspoon

                              (Edit: Beaten to the punch. I should have refreshed before posting.)

                              OK, so in your SQL database, you stored the YearMonth as a TEXT field like '2009/12'. You want to load the current month and the most recent three months. So since today is in month 2009/12, you want your select to include:

                              WHERE YearMonth IN ('2009/12','2009/11','2009/10','2009/09')

                              And then on January 1, you want it to automatically change to:

                              WHERE YearMonth IN ('2010/01','2009/12','2009/11','2009/10')

                              If so, then yes, you could handle it in the DBMS with getdate() and the like. But yes, you can also handle it by building a variable in your script.

                              LET MonthList = '(' & CHR(39) & date( today() ,'YYYY/MM') & CHR(39)
                              & ',' & CHR(39) & date(addmonths(today(),-1),'YYYY/MM') & CHR(39)
                              & ',' & CHR(39) & date(addmonths(today(),-2),'YYYY/MM') & CHR(39)
                              & ',' & CHR(39) & date(addmonths(today(),-3),'YYYY/MM') & CHR(39) & ')';

                              And then referencing the variable in your select:

                              SQL SELECT * FROM blahblah
                              WHERE YearMonth IN $(MonthList)