Solved

Date question in Crystal Reports / Sequel

Posted on 2015-02-18
7
243 Views
Last Modified: 2015-02-19
I want an start date of 01/01/1900 (think I can do that)  and end date that is two weeks prior to the beginning of this week.  This may sound simple to some, but I can't figure it out!

Today is 2/18/15.  I need the data from 01/01/1900 to 2/1/15.

Tomorrow the end date would be 2/2/15, etc.
0
Comment
Question by:Becky Edwards
7 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40617963
If you wish to achieve this using SQL (which you can) you need to tell us what brand of database is being used as each one is different for these date operations.

e.g.
in Microsoft SQL Server the current date and time is returned by GETDATE()
in the Oracle rdbms the current date and time is returned by SYSDATE
in MySQL there are options for current date and time: NOW() CURDATE() CURTIME()
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40618179
In Crystal you can use the SELECT EXPERT and have a selection formula like

{YourDateField} >= Date(2015,1,1) AND {YourDateField} <= CurrentDate -14

You could also use DateAdd

{YourDateField} >= Date(2015,1,1) AND {YourDateField} <=  DateAdd('w',-2,CurrentDate)

mlmcc
0
 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
ID: 40618560
You say that you want the date that's "two weeks prior to the beginning of this week", but then you say that you want 02/01 for 02/18, and 02/02 for 02/19 (the next day).  If you're really looking for a date that's relative to the beginning of the week, the target date won't change between 02/18 and 02/19.  It would be 02/01 for both days.

 If you actually do want the date that's 2 weeks before the beginning of this week, and assuming that your week starts on Sunday, you could use this formula:

CurrentDate - DayOfWeek (CurrentDate) - 13


 CurrentDate - DayOfWeek (CurrentDate) gives you the last day of the previous week, so subtracting 14 from that would give you the last day of the week, 2 weeks earlier.  So I only subtract 13, to get the first day of the week after that.

 James
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 34

Expert Comment

by:James0628
ID: 40618566
mlmcc,

 I'm guessing that Date(2015,1,1) should have been Date(1900,1,1).

 And, of course, subtracting 2 weeks from today doesn't seem to be what she's looking for, although, as mentioned in my last post, it's unclear exactly what she's looking for.

 James
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40618815
You are right, I misread the 1 Jan 1900.

If she needs dates greater than 1 Jan 1900 then unless that is a default date for the system, there is no need to test  for that since all dates will be.

mlmcc
0
 

Author Closing Comment

by:Becky Edwards
ID: 40619635
Thank you very much.  That worked perfectly James.  And thank you Mlmcc for pointing out I don't really need a beginning date! to my date selection.  Duh.
0
 
LVL 34

Expert Comment

by:James0628
ID: 40619658
You're welcome.

 James
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
How to increase the row limit in Jasper Server.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question