Convert Function and Dates with DateTime 1

[Krystoff]

Hey hey all!

I have some data in a table that contains two types of dates.

1) Just the date 2/6/2004 00:00:00
2) Date w/ time : 2/6/2004 11:15:04

When I run this where statement against the date it will only give me back the date if I use a single date for @BEgDate and @EndDate.

WHERE (Convert(DateTime, dbo.Data_Cust.Cre_date, 102) between @BegDate and @EndDate)

What can I do to get all things for the date of 2/6?

Any help is mucho appreciated!

Chris

 

[ClaireHCM]

WHERE Convert(varchar(22),dbo.Data_Cust.Cre_date, 102) =
convert(varchar(22),@begindate,102)

by this way, you dont have to care about the time portion, it filters only by the date portion.

 

[Krystoff]

Thanks!

Thats what I was looking for.

Quick question, why would you convert it to Varchar and not to DateTime? Seems backwards to me but it worked brillantly

Chris

 

[SQLBill]

DATETIME datatype compares the whole date and time.

Using DATETIME 2/6/2004 00:00:00 and 2/6/2004 11:15:04 are two different things during a comparison.

Using VARCHAR you can CONVERT the DATETIME to just the portion you want to compare, so:

2/6/2004 00:00:00 can be CONVERTed to 2/6/2004
2/6/2004 11:15:04 can be CONVERTed to 2/6/2004
and both become equivelent during a comparison.

-SQLBill

 

[Krystoff]

Interesting, I will remember that for future reference. Thanks again!

Chris