SQL date

[JEnright1]

I have an SQL formula

((LAWSON.PAYMASTR.CHECK_DATE)={d '2012-07-19'}) that is not working. I am using Crystal 10. Can I create an SQL statement that would run the select statement with the check date = 7/19/2012? Thank You.

 

[djj55]

I would suggest asking this in the Crystal forum as T-SQL will not always work in Crystal.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

I suspect your problem is that the CHECK_DATE column probably has a time component to it. When you don't specify the time, it defaults to midnight, so 2012-07-19 does not equal 2012-07-19 10:15 AM.

Try this...

Replace:

((LAWSON.PAYMASTR.CHECK_DATE)={d '2012-07-19'})

With:
[tt]
(
(LAWSON.PAYMASTR.CHECK_DATE)[!]>=[/!]{d '2012-07-19'}
AND
(LAWSON.PAYMASTR.CHECK_DATE)[!]<[/!]{d '2012-07-[!]20[/!]'}
)
[/tt]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom