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

Transact-SQL
21

You can copy and paste the code below into an SSMS window and run it. It does what I understood you want to do. If this works, compare with your actual data and see what the difference is - perhaps two digit years? Or some other anomaly in the data?

CREATE TABLE #tmp(DateFromYYYY INT, DateFromMM INT, DateFromDD INT,
	DateToYYYY INT, DateToMM INT, 	DateToDD INT );
	
INSERT INTO #tmp VALUES
(2010,12,31, 2017,1,1),  -- this should pass the criteria
(2016,10,2, 2017,11,10), -- this will not pass the criteria
(2010,12,31, 2016,1,19)  -- this will not pass the criteria

-- this query will return one row.
SELECT * FROM
    #tmp
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();
22

Here is my live data:

DATEFROMDD DATEFROMMM DATEFROMYYYY DATETODD DATETOMM DATETOYYYY
23 12 2015 23 1 2016
1 1 2015 21 6 2016
1 1 2015 21 1 2016
1 1 2015 21 1 2016
1 1 2015 21 1 2016
1 1 2016 21 1 2016

This new query you pasted now works:

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

This loads 6 records. Expected result. I take it I need the two conditions?

23

Yes, you need both conditions.

25

Apologies, I've just noticed that this last query does not work. I had the query cached and forgot to remove :frowning:

I am only getting two records back with my own live data, above, not the 6 that there should be. It looks like this query does not produce the correct results. Perhaps you can create a table with my data and try your query?

The result is the first row and the second row of my data only:

1 1 2015 21 6 2016 1
23 12 2015 23 1 2016 1

26

I see you missed the +1 on the to date.
Add to DateToDD + 1
This puts the date after todays date.

A test

DECLARE @x TABLE (DATEFROMDD INT,DATEFROMMM INT,DATEFROMYYYY INT,DATETODD INT,DATETOMM INT,DATETOYYYY INT); 

INSERT INTO @x 
VALUES
(23,12,2015,23,1,2016),
(1,1,2015,21,6,2016),
(1,1,2015,21,1,2016),
(1,1,2015,21,1,2016),
(1,1,2015,21,1,2016),
(1,1,2016,21,1,2016);

SELECT *, 
	CAST(CAST(DateFromYYYY * 10000 + DateFromMM * 100 + DateFromDD AS VARCHAR(8)) AS DATE) FromDate,
	CAST(CAST(DateToYYYY * 10000 + DateToMM * 100 + DateToDD AS VARCHAR(8)) AS DATE) ToDate,
    CASE WHEN CAST(CAST(DateFromYYYY * 10000 + DateFromMM * 100 + DateFromDD AS VARCHAR(8)) AS DATE) <= GETDATE() THEN 1 else 0 end as xx,
	CASE WHEN CAST(CAST(DateToYYYY * 10000 + DateToMM * 100 + DateToDD + 1 AS VARCHAR(8)) AS DATE) >= GETDATE() THEN 1 else 0 end AS yy

FROM @x;
27

If I use this against my data, I get the error:

[SQLServer JDBC Driver][SQLServer]An expression of non-boolean type specified in a context where a condition is expected, near 'FromDate'

It works within a script window in Enterprise Manager ok, BUT if I change all end dates to 20,1,2016 then it still gives 6 results, when it should give none because all are in the past. Confused...

Please can you give an example where a specific table is used, rather than an all-in-one self-contained query, so I can get the syntax correct for my scenario.

See screenshot: http://i63.tinypic.com/20gwut.png

28

Your code doesn't check the dates as you stated you wanted to check them. Good luck with this.

29

Sorry, whose code doesn't check the dates as stated? I think I have explained the requirement in great detail. The query needs to return all records where the current date falls within the bounds of the dates in the table. My screenshot shows the query above does not do this.

30

I think a combination of factors is leading to your frustration. Each of us looks at it for a few minutes, and gives our "expert" opinion :slightly_smiling: And, each of those can be made to work with a slight tweaking.

In the code below, I have added couple of edge cases to make sure that it works as expected in those scenarios. Copy this and run, and if it does not work, post back.

CREATE TABLE #tmp(DateFromYYYY INT, DateFromMM INT, DateFromDD INT,
	DateToYYYY INT, DateToMM INT, 	DateToDD INT );
	
INSERT INTO #tmp 
(DateFromDD, DateFromMM, DateFromYYYY, DateToDD, DateToMM, DateToYYYY)
VALUES
(23,12,2015,23,1,2016),
(1,1,2015,21,6,2016),
(1,1,2015,21,1,2016),
(1,1,2015,21,1,2016),
(1,1,2015,21,1,2016),
(1,1,2016,21,1,2016),
(21,1,2016,21,1,2016), -- added this edge case
(1,1,2016,20,1,2016), -- added this edge case
(21,1,2016,30,1,2016) -- added this edge case

-- construct the dates, so we can look at them.
SELECT *,
	CAST(CAST(DateFromYYYY * 10000 + DateFromMM * 100 + DateFromDD AS VARCHAR(8)) AS DATE) ,
	CAST(CAST(DateToYYYY * 10000 + DateToMM * 100 + DateToDD AS VARCHAR(8)) AS DATE)
FROM
	#tmp;
	
-- Today is Jan 21, 2016, so all except one of the records should be returned.
-- I fyou were to run this tomorrow (Jan 22, 2016), you will get only fewer records
SELECT 
	*
FROM
    #tmp
WHERE
    CAST(CAST(DateFromYYYY * 10000 + DateFromMM * 100 + DateFromDD AS VARCHAR(8)) AS DATE) <= CAST(GETDATE() AS DATE)
	AND CAST(CAST(DateToYYYY * 10000 + DateToMM * 100 + DateToDD AS VARCHAR(8)) AS DATE) >= CAST(GETDATE() AS DATE);
	
DROP TABLE #tmp;
1 Like
31

thanks James, I have inserted this into the script and it returns the correct records. I'll check again tomorrow and report back. Can you please explain how your query works. Is it using epoch-style values (hence the multiplications). Thank you.