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?
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
0
themrrobertCommented:
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)
2
PadawanDBAOperational DBACommented:
why not just compare it?

where sessionStart >= getDate()

Open in new window

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.

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

WHERE DATEDIFF(MINUTE, GETDATE(), sessionStart) > 0
0
erikTsomikSystem Architect, CF programmer Author Commented:
@PadawanDBA

Can not use your solution because it does not do the time check
0
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:

WHERE sessionStart >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

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").
0
erikTsomikSystem Architect, CF programmer Author Commented:
this expression
WHERE sessionStart >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

does not work at all it gives the past dates
0
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
values
  ( '2015-10-28 10:20:00.000' )

select
  '>=',
  *
from
  #dateTest
where
  dateVal >= getDate()

union all

select
  '<=',
  *
from 
  #dateTest
where
  dateVal <= getDate();

Open in new window



Go look for yourself: http://sqlfiddle.com/#!6/8ef17/2
0
PortletPaulfreelancerCommented:
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?
0

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 GargBusiness Intelligence DeveloperCommented:
HI,

What is the datatype for column "sessionStart" ?

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

Then

Select * from table Where sessionStart > getdate()

will  definitely work.
0
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.

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.