Help : Difference between the 2 sql statements

What is the Difference in this 2 sql statements.

SQL 1
============
select * from table_name a
where
      
      CONVERT(datetime, CONVERT(datetime, dateAdd(dd,datediff(dd,0,START_DATE_OF_TRAVEL),0))) >=       CONVERT(datetime, CONVERT(datetime, dateAdd(dd,datediff(dd,0,@START_DATE_OF_TRAVEL),0))) AND
      ( a.START_DATE_OF_TRAVEL >= @START_DATE_OF_TRAVEL and a.START_DATE_OF_TRAVEL <= @TRAVEL_END_DATE) AND
      datepart(dw, a.START_DATE_OF_TRAVEL) between 2 and 6

SQL 2
=========
select * from table_name  a where
      A.WORKFLOW_STATUS_ID=1 AND
      USER_ID <> @USER_ID AND
      CONVERT(datetime, CONVERT(datetime, dateAdd(dd,datediff(dd,0,START_DATE_OF_TRAVEL),0))) >=       CONVERT(datetime, CONVERT(datetime, dateAdd(dd,datediff(dd,0,@START_DATE_OF_TRAVEL),0))) AND
      ( a.START_DATE_OF_TRAVEL >= @START_DATE_OF_TRAVEL and a.START_DATE_OF_TRAVEL <= @TRAVEL_END_DATE)AND
      datepart(dw, a.START_DATE_OF_TRAVEL) in (1,7)

Please explain with eg. thanks
TECH_NETAsked:
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.

kevin_uCommented:
I assume you've detected this difference.

 A.WORKFLOW_STATUS_ID=1 AND
      USER_ID <> @USER_ID AND

In addition, the last item:
datepart(dw, a.START_DATE_OF_TRAVEL) between 2 and 6
matches dates of travel of monday thru friday

compared to
datepart(dw, a.START_DATE_OF_TRAVEL) in (1,7)
matches dates of travel of saturday and sunday

0
TECH_NETAuthor Commented:
Just wanted to make sure that the dates of travel for the 1sql was monday thru friday
and the latter was saturday/sunday.

The
 A.WORKFLOW_STATUS_ID=1 AND
      USER_ID <> @USER_ID AND
was my typo. I just omiited it out in the first one.

Anyway, was just curious since i was able to retrieve any results. Maybe my dates format was messed up.(CONVERT(datetime)
0
TECH_NETAuthor Commented:
the start_date_of_travel is stored in the following format
2009-07-06 15:11:03.000
2009-07-06 16:49:54.000
etc

I really want the date comparison on the date only.
With my sql, these dates get rounded to
2009-07-06 00:00:00.000
2009-07-06 00:00:00.000

and the sql query ( if replace the start-date with getdate() should return 2 records.
select CONVERT(datetime, CONVERT(datetime, dateAdd(dd,datediff(dd,0,getdate()),0))) from COMMUTER_REQUESTS_VIEW a where
 
      CONVERT(datetime, CONVERT(datetime, dateAdd(dd,datediff(dd,0,START_DATE_OF_TRAVEL),0))) >=       CONVERT(datetime, CONVERT(datetime, dateAdd(dd,datediff(dd,0,getdate()),0))) AND
      ( a.START_DATE_OF_TRAVEL >= getdate() and a.START_DATE_OF_TRAVEL <= getdate()) AND
      datepart(dw, a.START_DATE_OF_TRAVEL) between 2 and 6

and that is not happening.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

kevin_uCommented:
Are you asking another question?  I am unclear if you want me to do more.
0
TECH_NETAuthor Commented:
yes, the first queston arose from my question 2 which i assumed (if the explaination provided for the question 1 was the correct one - which i do agree), should have provide me with 2 records from my database.

0
kevin_uCommented:
if this is still part of your query, it won't work.
( a.START_DATE_OF_TRAVEL >= getdate() and a.START_DATE_OF_TRAVEL <= getdate()) AND

this will only work if start_date_of_travel is the current date and time exactly.
0
TECH_NETAuthor Commented:
how can i get compare with the date part of the date
0
kevin_uCommented:
You've already answered that yourself:

CONVERT(datetime, CONVERT(datetime, dateAdd(dd,datediff(dd,0,START_DATE_OF_TRAVEL),0)))

>=  CONVERT(datetime, CONVERT(datetime, dateAdd(dd,datediff(dd,0,getdate()),0)))

That part will work just fine.

I'm not clear on what you're trying to accomplish.  Maybe some sample data would help.
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
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
Microsoft SQL Server 2005

From novice to tech pro — start learning today.