Solved

Date question in Crystal Reports / Sequel

Posted on 2015-02-18
7
241 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

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.

Question has a verified solution.

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

Suggested Solutions

Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
How to increase the row limit in Jasper Server.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

896 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now