Extract Date Only From Date/Time Field 1

[jmiskey]

We have a Date/Time Stamp field in our database. I need to extract just the date portion of that field (as date, not text), so we can do queries on it (allow users to enter a date and have it return all records entered on that date).

How can I do this?

Thanks.

 

[gmmastros]

From a performace perspective, it's best to handle this NOT by stripping the time component, but by using it to your advantage.

Ex.

[tt][blue]
Select *
From Table
Where DateField >= @StartDate
And DateField < @EndDate + 1
[/blue][/tt]

When structuring your query this way, indexes (if they exist) on the date field will be used and performance will be better.

Notice that Greater Than Or Equal To is used for the start date and Less Than EndDate + 1 is also used.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

To answer your original question...

Select DateAdd(Day, DateDiff(Day, 0, [!]GetDate()[/!]), 0)

You can include this in a query by replacing GetDate() with your column name.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jmiskey]

I agree, unfortunately I do not think that is an option I have at my disposal.

We are using software that uses SQL. It has a built in Report feature, with a built GUI criteria section. On the Criteria section, it allows you to enter a date and it will compare the date entered to any field or variable you designate (what I am trying to create).

99% of the time, users are going to want to see the records posted today or yesterday (but only on one day), so they are going to select the "Equals to" option and enter their date. They are not very computer savvy, so I really can't ask them to use things like Greater Than (which wouldn't work anyway if they ONLY wanted to see Yesterday, or some other past day's postings).

So, if every date in SQL has a time component (which I think it does), then I believe I just need to convert the Date/Time Stamp to change the time piece to 0:00? Does that sound right?

The question is how?

 

[jmiskey]

gmmastros,

Brilliant! I think that does what I need it to. I tested it out in an Ad Hoc Query, and it appears to work. I'll see if it works when I incorporate it into the Report program.

Thank you!