Date question in Crystal Reports / Sequel

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.
Becky EdwardsEpic Clarity DeveloperAsked:
Who is Participating?
 
James0628Connect With a Mentor Commented:
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
 
PortletPaulfreelancerCommented:
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
 
mlmccCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
James0628Commented:
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
 
mlmccCommented:
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
 
Becky EdwardsEpic Clarity DeveloperAuthor Commented:
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
 
James0628Commented:
You're welcome.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.