How do I show items dated 1 day from current date?

Question

Post reply

How do I show items dated 1 day from current date?

simflex-897410

Hall of Fame

Points: 3881
More actions
March 4, 2010 at 2:28 pm

#386439

Greetings experts,
I have strongly very much with this query all day today.
I have a fieldname of datetime datatype called FunctionDate.
Values are entered on a daily basis.
So we have sample data like this:
3/1/2010
3/2/2010
3/3/2010
3/4/2010
3/5/2010
3/6/2010
etc
etc
So there are daily date entries; just dates, no time
All I am trying to do is show records where functionDate is tomorrow.
In other words, when the users query records, they don't want today's or yesterday's records.
They want records where functionDate > GetDate() so records would look like this:
FunctionDate LastName FirstName
3/5/2010 Doe Jane
3/6/2010 Placco Mike
etc
Notice that records displayed begins from 3/5/2010, not today's date which is 3/4/2010.
I have explained as simply as I can.
This is what I have tried and has not worked:
Select* from mytable
WHERE functionDate > CONVERT(varchar, GETDATE()+1, 112)
Select* from mytable
WHERE functionDateselect > dateadd(d, 1, GETDATE())
Select* from mytable
WHERE (functionDate > dateadd(d, 1, floor(cast(GETDATE() as float))))
I just kept getting dates that are more than a week out and I know we have data dated 3/5/2010 and later.
Thanks a lot in advance

Viewing 15 posts - 1 through 15 (of 47 total)

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

sturner SSC-Insane Points: 22411 More actions · Answer 1

try this:

Select* from mytable

WHERE functionDate > CONVERT(char(10),getdate(),101)

that assumes all times are 00:00:00

The probability of survival is inversely proportional to the angle of arrival.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Respectfully, have you looked at the DateDiff Function?

http://msdn.microsoft.com/en-us/library/ms189794.aspx

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

LutzM SSC Guru Points: 107049 More actions · Answer 3

There are some confusing statements in your epost:

"fieldname of datetime datatype" vs. "daily date entries; just dates, no time"

That's simply impossible. Either you have a DATE datatype (assuming you're using SS2K8, since you posted in the SS2K8 forum) or you have values like

"2010-03-04 00:00:00.000"

Your query

Select* from mytable

WHERE functionDateselect > dateadd(d, 1, GETDATE())

normally should work and return all data larger than the day after tomorrow. The reason is you add 1 day to the current day and time. So, instead of getting "2010/03/05" you'll get "2010/03/05 10:30PM".

To avoid this, you could "normalize" the getdate value and use a query like:

WHERE functionDateselect > dateadd(dd,datediff(dd,0,getdate()),0)

This will return values larger than today (assuming you don't use the time portion).

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]

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 4

Thanks a lot Lutz

On the db, we have functionDate which records only date portion as in 3/4/2010.

Then there is the time portion which records only time as in 2:15:13PM(AM).

I just wanted to clarify that functionDate has only date, no time, no 00.00.00.

Thanks for your assistance, particulary the time you took to explain things to me.

Thanks Turner for the code.

Also, lmu92, thanks a lot as well.

I apologize for any confusion.

What I meant by daily date entry is that every day, new data is entered into the db and that data includes values for functionDate.

Also, as explained to Lutz, functiondate does not have any time; just dates as in 2/1/2010.

Then there is a time fieldname that records time.

Perhaps, it would have made more sense to just one fieldname that records both date and time but I didn't design the db.

As for where I posted, I saw T-SQL as the forum name.

So, I assumed I could post here.

Sorry, if I posted in wrong forum

Again, I truly appreciate all the assistance.

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 5

simflex-897410

Hall of Fame

Points: 3881

March 4, 2010 at 6:58 pm

#1128920

Thanks Welsh.

jitukolte Grasshopper Points: 13 More actions · Answer 6

hi

you can do this by

Select* from mytable

WHERE functionDate>Getdate()

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 7

Unfortunately, unless your column is defined as a date data type (only available in SQL 2008), then you do not have a date. You have a string representation of data that can be interpreted as a date, maybe.

So, the problem you are encountering is due to the how you are comparing. You are not comparing dates, but rather are comparing string values.

Convert the column to a valid datetime (or date) data type and then compare. For example:

SELECT {columns}

FROM table

WHERE cast(FunctionDate As datetime) > dateadd(day, datediff(day, 0, getdate()) + 1, 0)

The calculate with dateadd will give you tomorrow morning at midnight (e.g. 2010-03-05 00:00:00.000).

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Grey Cat Mr or Mrs. 500 Points: 514 More actions · Answer 8

Does this work?

select * from table where functionDate > convert(date,GETDATE()+1)

LutzM SSC Guru Points: 107049 More actions · Answer 9

Grey Cat (3/5/2010)
Does this work?
select * from table where functionDate > convert(date,GETDATE()+1)

It depends.

I asked before: what is the data type of functionDate?

You stated it would be datetime, but that doesn't comply with your other statements. Please clarify.

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]

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 10

You are right, Lutz. I stated that the fieldname - functionDate is of dateTime datatype.

That is correct.

The other statements, as you correctly pointed out created contradictions and confusion.

Sorry about that.

LutzM SSC Guru Points: 107049 More actions · Answer 11

Ok, if the column is already datetime, you don't need to convert it.

So, the following will work:

select * from table where functionDate > date +1

BUT, this will usually end up with selecting data greater than the day after tomorrow, because

SELECT GETDATE()+ 1

will result in '2010-03-07 00:02:25.893' (query run on '2010-03-06 00:02:25.893')

and you're running a query against a table with "normalized" datetime values (sse my example in an earlier post).

Therefore, the next value larger than this will be '2010-03-08 00:00:00.000'.

I posted a solution to avoid this earlier in this thread.

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]

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 12

Many thanks again Lutz but one more question.

Are you saying that just being of dateTime datatype, that last code you wrote would work even though, there is no time value on the db?

In other words, all the values look like:

3/1/2010.

No such thing as 3/1/2010 12:11:33

Essentially, your code is based solely on the fact that functionDate is of dateTime datatype, not necessarily that it has date and time values?

Sorry, I don't mean to create more confusion. I just want to clarify for my own benefit.

Again, thanks alot.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 13

sturner (3/4/2010)
try this:
Select* from mytable
WHERE functionDate > CONVERT(char(10),getdate(),101)
that assumes all times are 00:00:00

It also causes a costly implicit conversion. Use the method that Lutz posted instead.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 14

simflex-897410 (3/5/2010)
Many thanks again Lutz but one more question.
Are you saying that just being of dateTime datatype, that last code you wrote would work even though, there is no time value on the db?
In other words, all the values look like:
3/1/2010.
No such thing as 3/1/2010 12:11:33
Essentially, your code is based solely on the fact that functionDate is of dateTime datatype, not necessarily that it has date and time values?
Sorry, I don't mean to create more confusion. I just want to clarify for my own benefit.
Again, thanks alot.

If you are not seeing a time component to the column functionDate - then it is not defined as a datetime datatype. It would not just show up as a date only value. On SQL Server 2008 - you could have the above if the column is defined as a date data type.

So, if you column is datetime - I would expect to see the column value as: 2010-03-01 00:00:00.000. If the column is date - I would expect to see the column value as: 2010-03-01

With your column showing up as 3/1/2010 - I would say that it is not a date data type or a datetime data type which is why you are having so many problems.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs