Parameters for time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Parameters for time

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-26 : 07:00:45

laurie writes "I am trying to create a query with parameters for a datetime field that I have converted to time, however when I add the between statement it returns nothing. I pretty sure between isn't the option I want to be using but I am at a loss for what I should be using, any help is greatly appreciated

SELECT DISTINCT
hoursworked.timein
, Convert(Char(8), timeout, 8) as timeout

FROM
hoursworked
INNER JOIN hoursworkeddetails on hoursworked.hoursworkedid = hoursworkeddetails.hoursworkedid

WHERE
hoursworked.TimeIn between '5/2/04' AND '5/16/04'
AND TimeOut between '6:00:00' and '18:00:00'"

sorengi
Starting Member

48 Posts

Posted - 2004-05-26 : 15:15:30

If you are using a datetime datatype to store only time, this is impossible. It will always store a date with it (default 1900-01-01), and therefore you must use datepart when comparing to other values.

Can we see a couple rows of data ?

Thanks,

Michael D.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-26 : 15:17:36

Use CONVERT with a style that displays only time portion on TimeOut column.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-27 : 11:25:50

I'm thinking...


SELECT * FROM (
SELECT DISTINCT
	   h.timein
	 , CONVERT(varchar(25),GetDate(),111) + ' ' + CONVERT(varhar(8), timeout, 108) as timeout

      FROM hoursworked h
INNER JOIN hoursworkeddetails d 
	ON h.hoursworkedid = d.hoursworkedid
     WHERE h.TimeIn >  '5/2/04' 
       AND h.TimeIn <= '5/16/04 23:59:59') AS XXX
WHERE TimeOut >   CONVERT(datetime,CONVERT(varchar(25),GetDate(),111) + ' ' +'06:00:00')
  AND TimeOut <=  CONVERT(datetime,CONVERT(varchar(25),GetDate(),111) + ' ' +'18:00:00')

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources