• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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