Date-Time to Date Function

[jabedin]

Hi there,
I need to supply a date range through user prompt to select record of particular date range. Rather than converting datetime field into Character field is there any way to make the field as date only. As currently this a date time field, I need to supply the date and time info. When converting datetime into character field, its taking lot longer time than selecting throuhg date time.

I will highly appreciate your help.

Regards
Jay

 

[ESquared]

Don't make it a character field.

Instead, why not convert the user date from character to date and use a range?

For example, if the user enters the characters '12/8/2004' which comes in a varchar variable, @TheDate, you can do the following:

WHERE DateColumn >= @TheDate AND DateColumn < @TheDate +1

or @TheDate through the end of the month it is in:

WHERE DateColumn >= @TheDate
   AND DateColumn < DateAdd(m,DateDiff(m,0,@TheDate)+1,0)

or, let's say you wanted to select all of today's records with the GetDate() function... which has the time attached.

If GetDate returns '12/8/2004 17:11pm' you can do:

WHERE DateColumn >= DateAdd(d,DateDiff(d,0,GetDate()) ,0)
   AND DateColumn < DateAdd(d,DateDiff(d,0,GetDate()),0)

I put it in this form so you can use it as is for months or years or milliseconds, just change the "d" to the right datepart value (look up the functions in BOL). But since it's days you can actually simplify it in this case:

WHERE DateColumn >= DateDiff(d,0,GetDate())
   AND DateColumn < DateDiff(d,0,GetDate()) + 1

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)