Solved

Date question in Crystal Reports / Sequel

Posted on 2015-02-18
7
244 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

808 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