Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Help : Difference between the 2 sql statements

Posted on 2009-07-06
8
Medium Priority
?
253 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
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

824 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