We have a database with dates stored as day, month and year fields and need to build a query that returns a recordset for only those dates that fall within the current date. For some reason the query returns records that are always a day too late or a day too early. Has anyone got a better way of writing this query?
We are on MSSQL 2008r2. The query we have so far is:
select * from mydatabase where
DATEADD( day, -1, CAST(CAST(DateFromYYYY AS varchar) + '-' + CAST(DateFromMM AS varchar) + '-' + CAST(DateFromDD + 1 AS varchar) AS DATETIME) ) <= GETDATE() AND
DATEADD( day, -1, CAST(CAST(DateToYYYY AS varchar) + '-' + CAST(DateToMM AS varchar) + '-' + CAST(DateToDD AS varchar) AS DATETIME) ) >= GETDATE()
The fields we have are DateFromYYYY, DateFromMM amd DateFromDD and DateToYYYY, DateToMM and DateToDD and they are all varchars. We can't change the fields in the database as this is a legacy system. Basically we build each date string on-the-fly and then cast it to a DATETIME, and then compare to see if it falls within the current date using GETDATE(). Our results are never accurate unfortunately.
We have tried without the CAST, as it has been pointed out that we are casting varchars back to varchars, but without the CAST we get no results at all. Any help appreciated.
Do something like this. In the exaple below, DateCol is the date that you constructed using the year, month and date.
SELECT * FROM Tbl
WHERE
DateCol = CAST(GETDATE() AS DATE)
If there is a time portion in your DateCol (which in your case you don't), then you would need to do something like this: SELECT * FROM Tbl WHERE DateCol >= CAST(GETDATE() AS DATE) AND DATEADD( dd, 1, DateCol) < CAST(GETDATE() AS DATE)
Thanks for the reply James, however we don't have a DateCol field, we have separate fields for the day, month and year (two lots, one for the "from" date and one for the "to" date). Basically, we have a database of events that have a start date and an end date, and we just want to return all of those events that fall within the current day's date. How do we get this DateCol field you refer to and can you give an example with all our six fields?
declare @d int = 14
declare @m int = 3
declare @y int = 2016
select cast(cast(@y as char(4)) + '-' + cast(@m as varchar(2)) + + '-' + cast(@d as varchar(2)) as date)
select * from mydatabase
where cast(cast(DateFromYYYY as char(4)) + '-' + cast(DateFromMM as varchar(2)) + '-' + cast(DateFromDD as varchar(2)) as DATE) <= cast(getdate() as DATE) AND
cast(cast(DateToYYYY as char(4)) + '-' + cast(DateToMM as varchar(2)) + '-' + cast(DateToDD as varchar(2)) as DATE) >= cast(getdate() as DATE)
Am I correct in requiring <= and >= in the order shown to get the correct date range here? It seems to be picking up the correct dates so far. Thanks.
You don't need both conditions. The following will be sufficient
SELECT
*
FROM
mydatabase
WHERE
CAST(CAST(DateFromYYYY AS CHAR(4)) + '-' + CAST(DateFromMM AS VARCHAR(2))
+ '-' + CAST(DateFromDD AS VARCHAR(2)) AS DATE) = CAST(GETDATE() AS DATE)
Alternatively, you can use what @djj55 suggested like this:
SELECT
*
FROM
mydatabase
WHERE
CAST(CAST(DateFromYYYY*10000+DateFromMM*100+DateFromDD AS VARCHAR(8)) AS DATE)
= CAST(GETDATE() AS DATE)
Neither of these queries return any data when I tested. I need to find out if the current date falls inside any of the dates in the database. I don't see how those queries would do that unless they know the beginning and end (the range of dates), rather than looking for one date, and thus needing two condition parts.
SELECT * FROM YourTable
WHERE CAST(CAST(DateFromYYYY*10000+DateFromMM*100+DateFromDD AS VARCHAR(8)) AS DATE) <= GETDATE()
AND CAST(CAST(DateToYYYY*10000+DateToMM*100+DateToDD AS VARCHAR(8)) AS DATE)+1 >= GETDATE()
No. Never use a function against a table column unless you absolutely have to. If you do, SQL can't make best use of any potential index(es). [In technical terms, it's "nonsargable".]
The most efficient method is below. In this case, it is probably the clearest way as well:
SELECT *
FROM mydatabase
WHERE
DateFromYYYY = CAST(YEAR(GETDATE()) AS varchar(4)) AND
DateFromMM = RIGHT('0' + CAST(MONTH(GETDATE()) AS varchar(2)), 2) AND
DateFromDD = RIGHT('0' + CAST(DAY(GETDATE()) AS varchar(2)), 2)
I had missed that you have DateFromYYYY etc in one condition, and DateToYYYY etc in the other condition. So my conclusion that you can use equality was wrong. The latest query @djj55 posted should have the correct logic.
Im' pretty sure that if the column is not indexed, how you build the where clause is not so crucial (you're doing some kind of (perhaps partial) scan anyway). The OP didn't mention any indexes on those three columns. If they are indexed, then SARGability matters; if not, write it the simplest way possible.
Since I hate to repeat myself in code, I'd probably add a cross apply to do the conversion to a date type then refer to that.
Hi. Thanks so much for all these new suggestions. Alas, none worked. I'm not sure if I am explaining my problem correctly. They return no data. One query gives a database error too - see above.
In a nutshell, I have a series of records with start and end dates in a table, stored as separate mm, dd, yyyy varchar values, as explained previously. When I run my script I want all the records that the current date falls within. So if I had a record with a start date of 2016-01-19 and an end date of 2016-01-21, then I'd like my record to be returned because today is 2016-01-20. My record should also have been returned yesterday, on the 19th, and it should also be returned tomorrow too since it's the 21st. On the 22nd the record will not be returned as the end date was the 21st. We have lots of records with lots of start/end times, so we need all to be returned that are valid i.e. today's date falls inside the start/end dates (and inclusive of the start/end date too). Hope that explains it better. There is no index involved btw.
No, I do not totally understand this query. I understand a query with the current date falling inbetween two dates much more easily, although I appreciate your way might be more efficient. Efficiency does sometimes lose readability, I find. Please can you explain why you multiply the values, thank you.
select * from mydatabase
where CAST(CAST(DateFromYYYY*10000+DateFromMM*100+DateFromDD AS VARCHAR(8)) AS DATE) <= GETDATE() AND
CAST(CAST(DateToYYYY*10000+DateToMM*100+DateToDD AS VARCHAR(8)) AS DATE)+1 >= GETDATE()
Do you have data that matches the criteria in the table? Post some sample data from your table that you think should pass the filter criteria (the where clause conditions) that you have.
I am assuming that the name of your TABLE is "mydatabase". Do some experiments like shown below to see what you have in your table.
-- Look at a few examples to make sure that the data is in the expected format, and that
-- conversion is working correctly
SELECT TOP 10
DateFromYYYY,DateFromMM, DateFromDD,
CAST(CAST(DateFromYYYY * 10000 + DateFromMM * 100 + DateFromDD AS VARCHAR(8)) AS DATE) FromDate,
DateToYYYY,DateToMM,DateToDD,
CAST(CAST(DateToYYYY * 10000 + DateToMM * 100 + DateToDD AS VARCHAR(8)) AS DATE) ToDate
FROM
mydatabase
-- then see if there is any data that matches at least one of the criteria
SELECT TOP 10
*
FROM
mydatabase
WHERE
CAST(CAST(DateFromYYYY * 10000 + DateFromMM * 100 + DateFromDD AS VARCHAR(8)) AS DATE) <= GETDATE()
-- add the second criterion. (Second critierion below is slightly different from what djj55 posted)
SELECT TOP 10
*
FROM
mydatabase
WHERE
CAST(CAST(DateFromYYYY * 10000 + DateFromMM * 100 + DateFromDD AS VARCHAR(8)) AS DATE) <= GETDATE()
AND CAST(CAST(DateToYYYY * 10000 + DateToMM * 100 + DateToDD AS VARCHAR(8)) AS DATE) >= GETDATE();