?
Solved

Date question in Crystal Reports / Sequel

Posted on 2015-02-18
7
Medium Priority
?
264 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 49

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 101

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 35

Accepted Solution

by:
James0628 earned 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 35

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 101

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 35

Expert Comment

by:James0628
ID: 40619658
You're welcome.

 James
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 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