SQLTeam.com | Weblogs | Forums

Help with date query built out of separate day/month/year fields

sql2008r2

#1

Hi,

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.


#2

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)


#3

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?


#4

easy enough to get a date to compare to:

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)

#5

@gbritton, you got me thinking, what about

[code]declare @d int = 14
declare @m int = 3
declare @y int = 2016

select @y10000+@m100+@d;
select CAST(CAST(@y10000+@m100+@d AS VARCHAR(8)) AS DATE) [/code]


#6

ok, so I now have:

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.


#7

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)

#8

that should work too. Better actually since its locale-independent


#9

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.


#10

Have you tried?

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()

Do you understand what I have done here?


#11

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)

#12

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.


#13

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.


#14

I based my code on the actual, original q:

need to build a query that returns a recordset for only those dates that fall within the current date.

and the followup comment:

we just want to return all of those events that fall within the current day's date.

In that case, a simple equals condition should do: the TO date should never be less than the FROM date.

If the table is constantly searched by yyyy, mm and dd, it should very likely be clustered on them, whether it actually is or not.


#15

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.


#16

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.


#17

Btw, I also get error:

"[SQLServer JDBC Driver][SQLServer]Operand type clash: date is incompatible with int"


#18

Please show the code you used.


#19
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()

#20

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();