Not taking current date in sql query

Question

Post reply

Not taking current date in sql query

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
October 3, 2014 at 11:45 am

#299849

Hi,
I have a query where its populates data in the frontend.
when i run the below query the date column is showing from tomorrow not todays date.
select t2.LastName + ' '+ t2.FirstName as Name,
t1.UID,RTRIM(LTRIM(t1.Event)) 'Eve' ,
-- convert(varchar, t1.CDate, 111) as CDate
CAST(t1.CDate as date) as CDate
from mcal t1
inner join Usr t2
on t1.UID=t2.Login where CalDate >=GETDATE()
AND CDate <= DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 13)
order by Cdate ASC
Any help plz..

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Hard to tell without seeing table def and any sample data...

My pot shot: CalDate holds a date value, therewith excluding the current day using the WHERE clause CalDate >=GETDATE()

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 2

Hi,

In mcal table the Caldate is datetime and the data in the caldate column is getting as

2014-10-01 00:00:00.000

LutzM SSC Guru Points: 107049 More actions · Answer 3

So you're actually storing a date value in a datetime column.

The date value for today is '20141003' (ISO format YYYYMMDD), but the datetime value might be '20141003 10:20:00'.

Therefore, when you use WHERE CalDate >= GETDATE() , all values with the current date will be excluded and you'll only see values starting with tomorrows date.

You might want to try

WHERE CalDate >= DATEADD(dd,DATEDIFF(dd,0,getdate()),0)

Or even better, assign the value of the getdate() conversion in a separate variable and use the variable in your query.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 4

Is this condition Correct?

on t1.UID=t2.Login where CalDate >=GETDATE()

You are using Caldate > = Today's date...

Check this

Thanks.