?
Solved

Help : Difference between the 2 sql statements

Posted on 2009-07-06
8
Medium Priority
?
244 Views
Last Modified: 2012-05-07
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
0
Comment
Question by:TECH_NET
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 12

Expert Comment

by:kevin_u
ID: 24787421
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
 

Author Comment

by:TECH_NET
ID: 24787462
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
 

Author Comment

by:TECH_NET
ID: 24787532
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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
LVL 12

Expert Comment

by:kevin_u
ID: 24787605
Are you asking another question?  I am unclear if you want me to do more.
0
 

Author Comment

by:TECH_NET
ID: 24787908
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
 
LVL 12

Expert Comment

by:kevin_u
ID: 24787964
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
 

Author Comment

by:TECH_NET
ID: 24788258
how can i get compare with the date part of the date
0
 
LVL 12

Accepted Solution

by:
kevin_u earned 2000 total points
ID: 24788385
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question