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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
kevin_uConnect With a Mentor Commented:
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
 
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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
 
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
All Courses

From novice to tech pro — start learning today.