Another date problem - What am I doing wrong? 
Author Message
 Another date problem - What am I doing wrong?

Anyone please help me (Rookie in the midst);

The problem is I am trying to retrieve all the records for the day.  The
problem being is that the way the date and time (obviously) is in one
field.  All I need is the date not the time (of course).

The following is the stored proc, any suggestions?



SELECT * ,convert(varchar,TblHourresults.recordtime,107)
FROM tblHourResults INNER JOIN tblShiftResults ON
tblhourresults.buildordernumber = tblshiftresults.buildordernumber


return(0)
GO



Thu, 13 Jul 2000 03:00:00 GMT
 Another date problem - What am I doing wrong?

This is something I haven't really got to grips with yet either.  One
solution I did recently that does work is:

    AND   CONVERT(smalldatetime, CONVERT(varchar, DATEPART(hour,
I.LogTime)) + ":" + CONVERT(varchar, DATEPART(minute, I.LogTime))) >=
CONVERT(smalldatetime, CONVERT(varchar, DATEPART(hour, R.StartTime)) +
":" + CONVERT(varchar, DATEPART(minute, R.StartTime)))
    AND   CONVERT(smalldatetime, CONVERT(varchar, DATEPART(hour,
I.LogTime)) + ":" + CONVERT(varchar, DATEPART(minute, I.LogTime))) <=
CONVERT(smalldatetime, CONVERT(varchar, DATEPART(hour, R.EndTime)) + ":"
+ CONVERT(varchar, DATEPART(minute, R.EndTime)))

It looks a bit of a mess in this editor I know, but I'm sure you will be
able to make sense of it.  Basically it's part of the WHERE clause and
ensures I.LogTime is between R.StartTime and R.EndTime.  You could use
just the first line, substituting GetDate() for R.StartTime.

Actually I think this is an awful kludge, and I'd love to hear
alternative methods.

Cheers,
Tim.

Quote:
> Anyone please help me (Rookie in the midst);

> The problem is I am trying to retrieve all the records for the day.  The
> problem being is that the way the date and time (obviously) is in one
> field.  All I need is the date not the time (of course).

> The following is the stored proc, any suggestions?



> SELECT * ,convert(varchar,TblHourresults.recordtime,107)
> FROM tblHourResults INNER JOIN tblShiftResults ON
> tblhourresults.buildordernumber = tblshiftresults.buildordernumber



> return(0)
> GO



Fri, 14 Jul 2000 03:00:00 GMT
 Another date problem - What am I doing wrong?

Thank you Tim Elley

I will try it out and let you know how I made out

Thanks again



Fri, 14 Jul 2000 03:00:00 GMT
 Another date problem - What am I doing wrong?

You will need to convert the column also in the same format in order to get
an exact match (since you are storing date and time both in the table)

SELECT * ,convert(varchar,TblHourresults.recordtime,107)
FROM tblHourResults INNER JOIN tblShiftResults ON
tblhourresults.buildordernumber = tblshiftresults.buildordernumber





Fri, 14 Jul 2000 03:00:00 GMT
 Another date problem - What am I doing wrong?

Hi

Thanks to all that replied I found what my problem was, I had forgotton to

dahhhhh!!
Other than that it works great

Barb Springstead



Sat, 15 Jul 2000 03:00:00 GMT
 Another date problem - What am I doing wrong?

Actually I went back & had another look at your query, and looked up the
extra parameter (107) in the convert function.  I hadn't seen that before
and it simplifies things wonderfully.  Given this new knowledge (thank
you for alerting me to it <g>), I think Divya's suggestion is the
definitely the best option.

Regards,
Tim.

Quote:
> Thank you Tim Elley

> I will try it out and let you know how I made out

> Thanks again



Sun, 16 Jul 2000 03:00:00 GMT
 Another date problem - What am I doing wrong?

I have always understood that the SQL optimizer prefers not to see
functions used on columns that have indexes. In the case below, assuming
there is an index on the date column, I have used the syntax:
     ...


Am I gaining anything in performance by doing this instead of converting
the date, or have I been misled?

Quote:
> ----------

> Posted At:         Monday, January 26, 1998 02:05p
> Posted To:         programming
> Conversation:      Another date problem - What am I doing wrong?
> Subject:   Re: Another date problem - What am I doing wrong?

> You will need to convert the column also in the same format in order
> to get
> an exact match (since you are storing date and time both in the table)

> SELECT * ,convert(varchar,TblHourresults.recordtime,107)
> FROM tblHourResults INNER JOIN tblShiftResults ON
> tblhourresults.buildordernumber = tblshiftresults.buildordernumber

> (tblHourResults.LineIDCode =





Sun, 16 Jul 2000 03:00:00 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. Parameter Problem - What am I doing wrong?

2. Update Problem, What am I doing wrong?

3. what am I doing wrong?

4. What am I doing wrong?

5. What in the world am I doing wrong?

6. Simple one what am i doing wrong

7. What am I doing wrong?

8. What am I doing wrong???

9. what i am doing wrong?

10. What am I doing wrong?

11. I think I am doing something wrong

12. ADO and SQL: what am I doing wrong ?


 
Powered by phpBB® Forum Software