sql server filtering time

Question

Hi, I'm using SQL Server 2005, and I'm using this code:

SELECT DateTimeField
FROM MyTable
WHERE
CONVERT(VARCHAR, DateTimeField, 108) >= '00:00:00.000' 
AND CONVERT(VARCHAR, DateTimeField, 108) <= '06:59:59.999' THEN

My question is, why does SQL Server doesn't retrieve the 00:00:00.000 records.

---

Always remember to say "Thanks", clicking "Mark As Answer" and "Vote as Helpful" if a post answers your question.

Answer 1

Hi

See below

select CONVERT(VARCHAR, GETDATE(), 108) ='10:45:25', it doesnt rerutn the milli second part of the time.

E g:- select case when '00:00:00' >='00:00:00.000'  then 1 else 0 end

Thanks

Sudhev

---

programmer

Answer 2

Hi please try below code, it will work. You need to mention the character limit (i.e) varchar(12)

create table mytable
(
DateTimeField datetime
)

insert into mytable select '00:00:00.000'
insert into mytable select '11:00:00.000'
insert into mytable select '12:00:00.000'


SELECT CASE WHEN 
CONVERT(VARCHAR(12), DateTimeField, 108) >= convert(varchar,'00:00:00')
AND CONVERT(VARCHAR(12), DateTimeField, 108) <= '06:59:59.999' THEN
      'True'
ELSE
      'False'
END AS Test
FROM MyTable

drop table MyTable

---

Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

Answer 3

Converting datetime to varchar style 108 does not return the millisecond values.

Try removing the millisecond in your query like 

create table #sampleTable 
(
	datetime_field datetime
)

insert into #sampleTable 
select '2012-12-06 00:00:00.000'

select * from #sampleTable where CONVERT(VARCHAR, datetime_field, 108) >= '00:00:00'

Thanks.

---

Best Regards, Venkat

Answer 4

Remove .000 in your first criterion.

It should be convert(varchar(10), DateTimeField, 108) >='00:00:00'

Run this and you'll see that it doesn't show ms when you use 108 format.

select convert(varchar,CURRENT_TIMESTAMP,108)

11th attempt to post now after a thread's re-load.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog