Jimbo99999
asked on
SQL Query - Compare Date Part of DateTime Value in Table
Good Day Experts!
It feels like my little quandry is feasible, but I have been unable to find a solution for it.
I have SQL table data in a date column:
12/6/2013 11:48:03 AM
12/6/2013 10:05:43 AM
12/6/2013 9:25:07 AM
The User enters a Date for Start and End.
When I build my query, how do I just compare the date part of the above data?
If the User enters 12/6/2013 for both Start and End dates, I want to return all the above values.
Thanks,
jimbo99999
It feels like my little quandry is feasible, but I have been unable to find a solution for it.
I have SQL table data in a date column:
12/6/2013 11:48:03 AM
12/6/2013 10:05:43 AM
12/6/2013 9:25:07 AM
The User enters a Date for Start and End.
When I build my query, how do I just compare the date part of the above data?
If the User enters 12/6/2013 for both Start and End dates, I want to return all the above values.
Thanks,
jimbo99999
ASKER
Ok, ours is pre-2008.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Pre 2008 you can CAST(CAST(datefield as INT) as datetime) to strip off the time.
Casting to INT rounds up to the next day if the time is after 12:00PM
DECLARE @date DATETIME
SET @date = '1/1/2013 13:00'
SELECT CAST(CAST(@date AS INT) AS DATETIME)
returns '1/2/2013'
DECLARE @date DATETIME
SET @date = '1/1/2013 13:00'
SELECT CAST(CAST(@date AS INT) AS DATETIME)
returns '1/2/2013'
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If performance is an issue than manipulating your parameters would make more efficient use of an index on the date field.
SELECT @EndDate =
CASE
WHEN @StartDate = @EndDate THEN DATEADD(DAY, 2, @EndDate)
ELSE DATEADD(DAY, 1, @EndDate)
END
SELECT ...
FROM myTable
WHERE dateColumn >= @StartDate
AND dateColumn < @EndDate
SELECT @EndDate =
CASE
WHEN @StartDate = @EndDate THEN DATEADD(DAY, 2, @EndDate)
ELSE DATEADD(DAY, 1, @EndDate)
END
SELECT ...
FROM myTable
WHERE dateColumn >= @StartDate
AND dateColumn < @EndDate
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks everyone for your repsonses. We had other trouble last Friday that had to be put out. So, I am just gettign back to this little project of mine.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for all the help everyone. I have learned quite a bit about dates. I will file this question in my knowledge base for future reference.
Thanks,
jimbo99999
Thanks,
jimbo99999
SELECT ...
FROM myTable
WHERE CAST(dateColumn AS DATE) >= @StartDate
AND CAST(dateColumn AS DATE) <= @EndDate