SQL DateDiff

I am trying to get the data where session start is greater the getDAte(). The problem is I need to include the time as well .

For example, if right now 10/28/2015 11:20 AM and the session start at  0/28/2015 11:00 AM I do not want this session . If the session start at 10/28/2015 11:50 AM I do want this session and all the session that are greater than  10/28/2015 11:20 AM.

I have been using DateDiff but this only get me a date

WHere DATEDIFF(D, GETDATE(), sessionStart) >= 1
LVL 19
erikTsomikSystem Architect, CF programmer Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
WHere DATEDIFF(HH, GETDATE(), sessionStart) >= 24
why not do

WHERE sessionStart > GETDATE();

(you should use GETUTCDATE() if possible, but you'll need to make sure your times are in UTC. GETDATE() will use the local timezone of the server)
PadawanDBAOperational DBACommented:
why not just compare it?

where sessionStart >= getDate()

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Brian CroweDatabase AdministratorCommented:
DATEDIFF does not return a date it returns an integer representing the difference in what ever units you specifiy, in your case above it will return the number of days between the two values.  In your example they have the same date value so the difference will be zero between any DATETIME values on 10/28.

try this...

erikTsomikSystem Architect, CF programmer Author Commented:

Can not use your solution because it does not do the time check
Scott PletcherSenior DBACommented:
I suspect you want everything for today.  That is, from midnight on, not from the current time on.  Here's the best way to do that:


The second expression just very efficiently strips the time from GETDATE().

You should never use a function on a table column unless you absolutely must, because it can make the SQL far less efficient (in technical terms, the column argument becomes "nonsargable").
erikTsomikSystem Architect, CF programmer Author Commented:
this expression

does not work at all it gives the past dates
PadawanDBAOperational DBACommented:
Just for clarification, a straight comparison of two datetime data types does not ignore the time resolution.  If there were an implicit conversion from a date data type to a datetime, that would be cast into a datetime with 00:00:00.000 for the time resolution portion and cause that behavior.  

I digress - the following works perfectly fine:

create table #dateTest
    dateVal datetime

insert into #dateTest
  ( '2015-10-28 10:20:00.000' )

  dateVal >= getDate()

union all

  dateVal <= getDate();

Open in new window

Go look for yourself: http://sqlfiddle.com/#!6/8ef17/2
PortletPaulEE Topic AdvisorCommented:
This statement is incorrect
>>"Can not use [ where sessionStart >= getDate() ] because it does not do the time check"

GETDATE() returns both current date and current time as a single datetime value
Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running.
You can directly compare a datetime column to GETDATE() and the comparison is precise down to milliseconds

i.e. there is no need for DATEADD() or DATEDIFF()

Just a thought: Are you dealing with multiple time zones in your data?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vikas GargAssociate Principal EngineerCommented:

What is the datatype for column "sessionStart" ?

If it is datetime and storing value in this format '2015-10-10 10:48:51.600'


Select * from table Where sessionStart > getdate()

will  definitely work.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.