CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Koen Hollander
Koen Hollander

Reputation: 1701

How to select stuff from a SQL database between dates?

On my work I try to create a query to get some information out of the SQL database. This is working good, but when I try to use a date range it stops with working.

I have figured out the DATEPART function, 

SELECT
 * 
FROM 
InlogLog as il 
INNER JOIN 
ComputerIdentity as ci
ON il.Computer = ci.Description 
WHERE ci.Location 
LIKE 'E18%'
AND ci.MakeModelID = 9
AND DATEPART(YY,il.Datum) = 2016
AND DATEPART(MM,il.Datum) = 10 
AND DATEPART(DD,il.Datum) = 03 
ORDER BY il.Computer

On the example above, I can't specify a range between two dates. I also have tried to use this example

SELECT
 * 
FROM 
InlogLog as il 
INNER JOIN 
ComputerIdentity as ci
ON il.Computer = ci.Description 
WHERE ci.Location 
LIKE 'E18%'
AND ci.MakeModelID = 9
AND il.Datum = '2016-06-17 09:57:00.000'

But this results in a error message

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

How can I make this next example work?

SELECT
 * 
FROM 
InlogLog as il 
INNER JOIN 
ComputerIdentity as ci
ON il.Computer = ci.Description 
WHERE ci.Location 
LIKE 'E18%'
AND ci.MakeModelID = 9
AND il.Datum '2016-06-17 09:57:00.000' BETWEEN '2016-06-18 00:00:00.000'

Upvotes: 1

Views: 96

Answers (4)

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

problem is in the syntax (il.Datum '2016-06-17 09:57:00.000' BETWEEN '2016-06-18 00:00:00.000') ,Modify your script like below.

If your column is varchar ,convert it to datetime.

SELECT * 
FROM 
InlogLog as il 
INNER JOIN 
ComputerIdentity as ci
ON il.Computer = ci.Description 
WHERE ci.Location 
LIKE 'E18%'
AND ci.MakeModelID = 9
AND CAST(il.Datum as datetime) BETWEEN '2016-06-17 09:57:00.000' AND  '2016-06-18 00:00:00.000'

Upvotes: 2

Mansoor
Mansoor

Reputation: 4192

 SELECT * FROM InlogLog as il INNER JOIN
 ComputerIdentity as ci 
 ON il.Computer = i.Description WHERE ci.Location
 LIKE 'E18%' AND ci.MakeModelID = 9 AND
 DATEDIFF(DAY,ilDatum,'2016-06-17') <= 0 AND
 DATEDIFF(DAY,ilDatum,'2016-06-18') >=0

Upvotes: 0

AT-2017
AT-2017

Reputation: 3149

If the il.Datum is varchar type, then you should do a casting for it as follows:

SELECT
 * 
FROM 
InlogLog as il 
INNER JOIN 
ComputerIdentity as ci
ON il.Computer = ci.Description 
WHERE ci.Location 
LIKE 'E18%'
AND ci.MakeModelID = 9
AND CONVERT(DATE, ilDatum) >= CONVERT(DATE, '2016-10-01')
AND CONVERT(DATE, ilDatum) <= CONVERT(DATE, '2016-10-04')

Note: I've converted the varchar to date type for the column il.Datum. Without conversion specifically for varchar type, you can't filter the date.

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

Looks like Datum is of varchar datatype where it should be of datetime datatype

Try this

SELECT
 * 
FROM 
InlogLog as il 
INNER JOIN 
ComputerIdentity as ci
ON il.Computer = ci.Description 
WHERE ci.Location 
LIKE 'E18%'
AND ci.MakeModelID = 9
AND il.Datum >='2016-06-17 09:57:00.000' il.Datum<= '2016-06-18 00:00:00.000'

Upvotes: 1

Related Questions