February 22, 2012 at 7:46 am
I am trying to write a query that pulls our new orders for the current day.
Where FM.Openeddate = getdate()
did not work. It took me an hour before randomly doing this
Where FM.Openeddate >= getdate()
and now it works. Any idea why pulling information for todays date needs to be >= and not just =? Thanks for your help.
February 22, 2012 at 8:05 am
Jeremy... (2/22/2012)
I am trying to write a query that pulls our new orders for the current day.Where FM.Openeddate = getdate()
did not work. It took me an hour before randomly doing this
Where FM.Openeddate >= getdate()
and now it works. Any idea why pulling information for todays date needs to be >= and not just =? Thanks for your help.
Function getdate() is retrieving the current date in the following format 2012-02-22 16:59:27.031. Check which values are exists in the field Openeddate of your table. You can see more details here -
February 22, 2012 at 8:16 am
Jeremy... (2/22/2012)
I am trying to write a query that pulls our new orders for the current day.Where FM.Openeddate = getdate()
did not work. It took me an hour before randomly doing this
Where FM.Openeddate >= getdate()
and now it works. Any idea why pulling information for todays date needs to be >= and not just =? Thanks for your help.
Are you sure that this is working for you? I would think what you really need something more like this:
WHERE FM.Openeddate >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
February 22, 2012 at 8:21 am
Try this :
WHERE CONVERT(DATE,FM.Openeddate)=CONVERT(DATE,GETDATE())
Jeremy... (2/22/2012)
I am trying to write a query that pulls our new orders for the current day.Where FM.Openeddate = getdate()
did not work. It took me an hour before randomly doing this
Where FM.Openeddate >= getdate()
and now it works. Any idea why pulling information for todays date needs to be >= and not just =? Thanks for your help.
February 22, 2012 at 8:22 am
Lynn Pettis (2/22/2012)
Jeremy... (2/22/2012)
I am trying to write a query that pulls our new orders for the current day.Where FM.Openeddate = getdate()
did not work. It took me an hour before randomly doing this
Where FM.Openeddate >= getdate()
and now it works. Any idea why pulling information for todays date needs to be >= and not just =? Thanks for your help.
Are you sure that this is working for you? I would think what you really need something more like this:
WHERE FM.Openeddate >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
I'm fairly new to this. What would that statement do?
February 22, 2012 at 8:28 am
Hakuna Matata (2/22/2012)
Try this :
WHERE CONVERT(DATE,FM.Openeddate)=CONVERT(DATE,GETDATE())Jeremy... (2/22/2012)
I am trying to write a query that pulls our new orders for the current day.Where FM.Openeddate = getdate()
did not work. It took me an hour before randomly doing this
Where FM.Openeddate >= getdate()
and now it works. Any idea why pulling information for todays date needs to be >= and not just =? Thanks for your help.
This worked. Thank you! Why exactly did i have to do this in the first place?
February 22, 2012 at 8:44 am
GETDATE() will retrieve date in the format 2012/02/22 21:23:33.256 (Includes Date & Time). So in your query when u say
FM.Openeddate=getdate() the date match should be exact up to the micro seconds ie, you are trying to do a date & time match.
whereas CONVERT(DATE,GETDATE()) will get you (2012/02/22) in date format not as DATETIME .
Hope I am clear:-)
Jeremy... (2/22/2012)
Hakuna Matata (2/22/2012)
Try this :
WHERE CONVERT(DATE,FM.Openeddate)=CONVERT(DATE,GETDATE())Jeremy... (2/22/2012)
I am trying to write a query that pulls our new orders for the current day.Where FM.Openeddate = getdate()
did not work. It took me an hour before randomly doing this
Where FM.Openeddate >= getdate()
and now it works. Any idea why pulling information for todays date needs to be >= and not just =? Thanks for your help.
This worked. Thank you! Why exactly did i have to do this in the first place?
February 22, 2012 at 8:45 am
Jeremy... (2/22/2012)
Lynn Pettis (2/22/2012)
Jeremy... (2/22/2012)
I am trying to write a query that pulls our new orders for the current day.Where FM.Openeddate = getdate()
did not work. It took me an hour before randomly doing this
Where FM.Openeddate >= getdate()
and now it works. Any idea why pulling information for todays date needs to be >= and not just =? Thanks for your help.
Are you sure that this is working for you? I would think what you really need something more like this:
WHERE FM.Openeddate >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
I'm fairly new to this. What would that statement do?
I don't have your from statement, so you will have to complete the following to see what is going on:
select top 20
FM.Openeddate,
GETDATE(),
DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
FROM
dbo.yourtable FM
;
After looking at the returned data, if you still have questions please feel free to ask.
February 22, 2012 at 9:00 am
getdate() retrieves the time too. So unless your orders were placed at this second, they're not going to return in the results section
February 22, 2012 at 9:08 am
The replie is quite simple when you go with finding the date as getdate(), the getdate will return you as 2012-02-22 10:03:50.033 if you look carefully it include yyyy-mm-dd hh:mm:ss.nano sec so when you search this value into your table you never gonna find the data tho the date is available for the givin date.
And when you done columnname >= getdate() it means any value equall or greater then (2012-02-22 10:03:50.033) this.. thus you may get few result but still few data may get skip due to the time below your given getdate()
If you convert the function as select convert(varchar(10),getdate(),103) you may get the just the date and you may compare directly with your table column and get the data for given date.
Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com
February 22, 2012 at 9:13 am
Thank you all for the explanations. Makes perfect sense now. 🙂
February 22, 2012 at 10:17 am
I dont mean to be pedantic but for accuracy I'd want to see this query as a between and in a stored procedure with default values so it can be reused for any day and cater for incorrect data (just in case)
February 22, 2012 at 10:22 am
MysteryJimbo (2/22/2012)
I dont mean to be pedantic but for accuracy I'd want to see this query as a between and in a stored procedure with default values so it can be reused for any day and cater for incorrect data (just in case)
What is a between?
It is in a stored procedure and a report has been created of it and put on the report server.
When you say using default values, do you mean something like:
DECLARE @today datetime
SET @today = CONVERT(DATE,FA.receiveddate)=CONVERT(DATE,GETDATE())
would that be correct? Then substitute @today in the where clause?
February 22, 2012 at 11:04 am
Jeremy... (2/22/2012)
MysteryJimbo (2/22/2012)
I dont mean to be pedantic but for accuracy I'd want to see this query as a between and in a stored procedure with default values so it can be reused for any day and cater for incorrect data (just in case)What is a between?
It is in a stored procedure and a report has been created of it and put on the report server.
When you say using default values, do you mean something like:
DECLARE @today datetime
SET @today = CONVERT(DATE,FA.receiveddate)=CONVERT(DATE,GETDATE())
would that be correct? Then substitute @today in the where clause?
I would not use between if I were to write this to return data for a specific date. I would use the follow snippet as a start:
declare @ReportDate datetime; -- Think of this as the input parameter requesting a specified date
declare @StartDate datetime,
@EndDate datetime;
-- Assume that the date entered may have time component that needs striping
set @StartDate = DATEADD(dd, DATEDIFF(dd, 0, @ReportDate), 0);
set @EndDate = DATEADD(dd, 1, @StartDate);
SELECT
--- columns to be returned here
FROM
--- table a (or tables)
WHERE
a.OrderedDate >= @StartDate and
a.OrderedDate < @EndDate -- followed by other sfilter conditions if needed
;
February 22, 2012 at 11:32 am
Lynn Pettis (2/22/2012)
Jeremy... (2/22/2012)
MysteryJimbo (2/22/2012)
I dont mean to be pedantic but for accuracy I'd want to see this query as a between and in a stored procedure with default values so it can be reused for any day and cater for incorrect data (just in case)What is a between?
It is in a stored procedure and a report has been created of it and put on the report server.
When you say using default values, do you mean something like:
DECLARE @today datetime
SET @today = CONVERT(DATE,FA.receiveddate)=CONVERT(DATE,GETDATE())
would that be correct? Then substitute @today in the where clause?
I would not use between if I were to write this to return data for a specific date. I would use the follow snippet as a start:
declare @ReportDate datetime; -- Think of this as the input parameter requesting a specified date
declare @StartDate datetime,
@EndDate datetime;
-- Assume that the date entered may have time component that needs striping
set @StartDate = DATEADD(dd, DATEDIFF(dd, 0, @ReportDate), 0);
set @EndDate = DATEADD(dd, 1, @StartDate);
SELECT
--- columns to be returned here
FROM
--- table a (or tables)
WHERE
a.OrderedDate >= @StartDate and
a.OrderedDate < @EndDate -- followed by other sfilter conditions if needed
;
You "could" do it either way; since BETWEEN is inclusive you would have to use a datetime with .997 ms. So 23:59:59.997.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply