[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

T-Sql DATE Question

I need a filter that gives me all transactions posted prior to the first day of the current month.
I am going to place this in the record selection of a Crystal 2008 Report.
How can I say that in Sequel?

The field is a DATE field.  

Date({CLARITY_TDL_AGE.POST_DATE}) < First day of current month
0
Becky Edwards
Asked:
Becky Edwards
1 Solution
 
Anthony PerkinsCommented:
CLARITY_TDL_AGE.POST_DATE < SELECT DATEADD(DAY, 1 - DAY(GETDATE()), DATEDIFF(DAY, 0, GETDATE()))
0
 
Becky EdwardsEpic Clarity DeveloperAuthor Commented:
Nope, sorry.  It keeps saying the ) is missing where the word GETDATE is.
0
 
mlmccCommented:
Are you trying to do this in Crystal or in MS SQL?

In Crystal
Date({CLARITY_TDL_AGE.POST_DATE})  < CurrentDate - (Day(CurrentDate) - 1)

Another way in Crystal

Date({CLARITY_TDL_AGE.POST_DATE})  <= Maximum(LastFullMonth)

The code acperkins provided is for use in SQL Server.

mlmcc

0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
Anthony PerkinsCommented:
>>The code acperkins provided is for use in SQL Server.<<
Exactly, I thought that is what they meant by "How can I say that in Sequel?"
0
 
Anthony PerkinsCommented:
Although on second thoughts it should have been:
CLARITY_TDL_AGE.POST_DATE < DATEADD(DAY, 1 - DAY(GETDATE()), DATEDIFF(DAY, 0, GETDATE()))
0
 
James0628Commented:
Just another option for CR:

Date ({CLARITY_TDL_AGE.POST_DATE}) < Date (Year (CurrentDate), Month (CurrentDate), 1)


 James
0
 
Becky EdwardsEpic Clarity DeveloperAuthor Commented:
Thanks everyone for your help!.  Sorry I wasn't clear on needing Crystal syntax.  The first one mimcc provided worked perfectly, so I haven't tried the others but I will keep all in reserve.

You are all awesome.  You make me look so good!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now