0

i query this SQL query:

SELECT * FROM [DB].[dbo].[Table]
WHERE [DATE] BETWEEN '01-01-2016' AND '31-03-2016'
AND ([TIME] >= '00:00:00' OR [DATE] > '01-01-2016')
AND ([TIME] <= '00:00:00' OR [DATE] < '31-03-2016');

But in SQL Studio the result to another date and time.

enter image description here

[DATE] = VARCHAR(10) (DD-MM-YYYY)
[TIME] = VARCHAR(10) (HH:MM:SS) 24h

How to fix ?

  • 1
    I don't understand (a) the purpose of your second and third where clauses, (b) what "another date and time" means, or (c) what "fix" means - how is this broken? Error message (and if so, what is it?), wrong data, something else? Can you show table structure, data types, sample data, desired results, and explain why you're using problematic regional formats like dd-mm-yyyy? Only YYYYMMDD is guaranteed safe in SQL Server. – Aaron Bertrand May 26 '16 at 18:04
  • 1
    In general your problem is using varchar(10) columns instead of date & time columns. Your current format means that '10-01-2001' > '01-01-2016' – Kenneth Fisher May 26 '16 at 18:07
  • 1
    Yeah ;) Anyway, Martin's answer may give some clue about the OP's endeavours. And I think that answer was posted before any mention of "date format". – ypercubeᵀᴹ May 26 '16 at 18:09
8

Priority 1: Fix the table. You should not be storing date and time data, in varchar columns, and quite possibly should not be separating date and time into their own separate columns. You should also avoid reserved words/keywords as columns, but I suspect you may have just dumbed down your actual table structure.

Priority 2: Stop using BETWEEN. This can cause all kinds of issues, especially if you fix the table and combine the date and time into a single column. The end of a range is different depending on the data type, but using >= beginning of the range and < beginning of the NEXT range will always work. Consider that the table has changed, you can say as follows (and I'm not sure what you're trying to do with your second and third where clauses, but you should just be able to look at the date column):

WHERE [DATE] >= '20160101'
  AND [DATE] <  '20160401';
-- note that I don't have to look at the TIME column at all
-- also note that I use a *SAFE* format for the string literal

Priority 3: Stop comparing dates as strings. If you can't fix the table, then your clauses need to first convert the string columns into valid date/time values, so you aren't comparing strings. And you need to use string formats that are safe in SQL Server; dd-mm-yyyy is not one of those formats - it can break (or just return the wrong data) if you change language settings, for example.

WHERE CONVERT(datetime, [DATE], 105) >= '20160101' 
  AND CONVERT(datetime, [DATE], 105) <  '20160401';
-- 103 makes sure the stored strings are interpreted as d/m/y

Additional reading:

  • I use SQL 2000 and date and time is not available :/ – Marvin Gerardin May 26 '16 at 18:23
  • 3
    @Marvin When you're using an ancient, unsupported version of SQL Server, please always tag the question correctly. Also, you missed part of my recommendation: combining them into a single datetime column. Why are they separate columns in the first place? – Aaron Bertrand May 26 '16 at 18:24
  • This working WHERE CONVERT (datetime, [DATE], 105) >= '20160101' AND CONVERT (datetime, [DATE], 105) <= '20160331' Is it possible to add hours – Marvin Gerardin May 26 '16 at 19:44
  • 3
    @Marvin You need to better define what you mean by "add hours" - also, please don't use <= '20160331', use < '20160401'. Please. The <= is bad for a reason. – Aaron Bertrand May 26 '16 at 20:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.