How do include TIME with my SQL query for finding data between two dates?

0

I'm trying to get data between two dates that should include seconds

I'm currently using the below code, which works fine; however now it needs to be more precise to include the time.

where h.creation_time >= date '2017-01-01'

and h.CLOSED_TIME >= date '2018-12-16' and h.CLOSED_TIME <= date '2018-12-17' 

Here is what I tried changing the code to:

where (h.creation_time >= date '2017-01-01')

and (h.CLOSED_TIME between '2018-12-16 18:19:00' and '2018-12-16 18:20:00')

I should expect the results of everything between 6:19pm and 6:20pm (one minute time frame). Instead it is spitting out the error:

ORA-01861: literal does not match format string

sql oracle

share|improve this question

edited Jan 2 at 23:27

sticky bit

15.7k111733

asked Jan 2 at 23:15

EricEric

112

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  what datatype are the values stored in?
  
  – javan.rajpopat
  Jan 2 at 23:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @javan.rajpopat they are stored as type DATE
  
  – Eric
  Jan 2 at 23:21
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  it should be in the data type DATETIME or TIMESTAMP to compare it by Time and Date.
  
  – javan.rajpopat
  Jan 2 at 23:23
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  The issue is that the DATE literal is for calendar dates only, it does not take a time-of-day component. The TIMESTAMP literal would do what you need; do not remove the keyword DATE, but instead replace it with TIMESTAMP. This has one disadvantage though - it relies on implicit conversion between date and timestamp data types. It may be best to use TO_DATE() with the appropriate format model, in this case 'yyyy-mm-dd hh24:mi:ss'.
  
  – mathguy
  Jan 2 at 23:28
  
  
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Question though - between a and b means >= a and <= b. If you use this for consecutive intervals, the endpoints will be double counted. The common (and correct) solution is to avoid the keyword between and to write the inequalities explicitly as >= a and < b - with strict inequality for the upper end point.
  
  – mathguy
  Jan 2 at 23:30
  

  
  
  
  

 | 
show 2 more comments

0

I'm trying to get data between two dates that should include seconds

I'm currently using the below code, which works fine; however now it needs to be more precise to include the time.

where h.creation_time >= date '2017-01-01'

and h.CLOSED_TIME >= date '2018-12-16' and h.CLOSED_TIME <= date '2018-12-17' 

Here is what I tried changing the code to:

where (h.creation_time >= date '2017-01-01')

and (h.CLOSED_TIME between '2018-12-16 18:19:00' and '2018-12-16 18:20:00')

I should expect the results of everything between 6:19pm and 6:20pm (one minute time frame). Instead it is spitting out the error:

ORA-01861: literal does not match format string

sql oracle

share|improve this question

edited Jan 2 at 23:27

sticky bit

15.7k111733

asked Jan 2 at 23:15

EricEric

112

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  what datatype are the values stored in?
  
  – javan.rajpopat
  Jan 2 at 23:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @javan.rajpopat they are stored as type DATE
  
  – Eric
  Jan 2 at 23:21
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  it should be in the data type DATETIME or TIMESTAMP to compare it by Time and Date.
  
  – javan.rajpopat
  Jan 2 at 23:23
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  The issue is that the DATE literal is for calendar dates only, it does not take a time-of-day component. The TIMESTAMP literal would do what you need; do not remove the keyword DATE, but instead replace it with TIMESTAMP. This has one disadvantage though - it relies on implicit conversion between date and timestamp data types. It may be best to use TO_DATE() with the appropriate format model, in this case 'yyyy-mm-dd hh24:mi:ss'.
  
  – mathguy
  Jan 2 at 23:28
  
  
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Question though - between a and b means >= a and <= b. If you use this for consecutive intervals, the endpoints will be double counted. The common (and correct) solution is to avoid the keyword between and to write the inequalities explicitly as >= a and < b - with strict inequality for the upper end point.
  
  – mathguy
  Jan 2 at 23:30
  

  
  
  
  

 | 
show 2 more comments

0

0

0

I'm trying to get data between two dates that should include seconds

I'm currently using the below code, which works fine; however now it needs to be more precise to include the time.

where h.creation_time >= date '2017-01-01'

and h.CLOSED_TIME >= date '2018-12-16' and h.CLOSED_TIME <= date '2018-12-17' 

Here is what I tried changing the code to:

where (h.creation_time >= date '2017-01-01')

and (h.CLOSED_TIME between '2018-12-16 18:19:00' and '2018-12-16 18:20:00')

I should expect the results of everything between 6:19pm and 6:20pm (one minute time frame). Instead it is spitting out the error:

ORA-01861: literal does not match format string

sql oracle

share|improve this question

edited Jan 2 at 23:27

sticky bit

15.7k111733

asked Jan 2 at 23:15

EricEric

112

I'm trying to get data between two dates that should include seconds

I'm currently using the below code, which works fine; however now it needs to be more precise to include the time.

where h.creation_time >= date '2017-01-01'

and h.CLOSED_TIME >= date '2018-12-16' and h.CLOSED_TIME <= date '2018-12-17' 

Here is what I tried changing the code to:

where (h.creation_time >= date '2017-01-01')

and (h.CLOSED_TIME between '2018-12-16 18:19:00' and '2018-12-16 18:20:00')

I should expect the results of everything between 6:19pm and 6:20pm (one minute time frame). Instead it is spitting out the error:

ORA-01861: literal does not match format string

sql oracle

sql oracle

share|improve this question

edited Jan 2 at 23:27

sticky bit

15.7k111733

asked Jan 2 at 23:15

EricEric

112

share|improve this question

edited Jan 2 at 23:27

sticky bit

15.7k111733

asked Jan 2 at 23:15

EricEric

112

share|improve this question

share|improve this question

edited Jan 2 at 23:27

sticky bit

15.7k111733

edited Jan 2 at 23:27

sticky bit

15.7k111733

edited Jan 2 at 23:27

sticky bit

15.7k111733

15.7k111733

asked Jan 2 at 23:15

EricEric

112

asked Jan 2 at 23:15

EricEric

112

asked Jan 2 at 23:15

EricEric

112

112

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  what datatype are the values stored in?
  
  – javan.rajpopat
  Jan 2 at 23:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @javan.rajpopat they are stored as type DATE
  
  – Eric
  Jan 2 at 23:21
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  it should be in the data type DATETIME or TIMESTAMP to compare it by Time and Date.
  
  – javan.rajpopat
  Jan 2 at 23:23
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  The issue is that the DATE literal is for calendar dates only, it does not take a time-of-day component. The TIMESTAMP literal would do what you need; do not remove the keyword DATE, but instead replace it with TIMESTAMP. This has one disadvantage though - it relies on implicit conversion between date and timestamp data types. It may be best to use TO_DATE() with the appropriate format model, in this case 'yyyy-mm-dd hh24:mi:ss'.
  
  – mathguy
  Jan 2 at 23:28
  
  
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Question though - between a and b means >= a and <= b. If you use this for consecutive intervals, the endpoints will be double counted. The common (and correct) solution is to avoid the keyword between and to write the inequalities explicitly as >= a and < b - with strict inequality for the upper end point.
  
  – mathguy
  Jan 2 at 23:30
  

  
  
  
  

 | 
show 2 more comments

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  what datatype are the values stored in?
  
  – javan.rajpopat
  Jan 2 at 23:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @javan.rajpopat they are stored as type DATE
  
  – Eric
  Jan 2 at 23:21
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  it should be in the data type DATETIME or TIMESTAMP to compare it by Time and Date.
  
  – javan.rajpopat
  Jan 2 at 23:23
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  The issue is that the DATE literal is for calendar dates only, it does not take a time-of-day component. The TIMESTAMP literal would do what you need; do not remove the keyword DATE, but instead replace it with TIMESTAMP. This has one disadvantage though - it relies on implicit conversion between date and timestamp data types. It may be best to use TO_DATE() with the appropriate format model, in this case 'yyyy-mm-dd hh24:mi:ss'.
  
  – mathguy
  Jan 2 at 23:28
  
  
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Question though - between a and b means >= a and <= b. If you use this for consecutive intervals, the endpoints will be double counted. The common (and correct) solution is to avoid the keyword between and to write the inequalities explicitly as >= a and < b - with strict inequality for the upper end point.
  
  – mathguy
  Jan 2 at 23:30
  

  
  
  
  

what datatype are the values stored in?

– javan.rajpopat
Jan 2 at 23:19

what datatype are the values stored in?

– javan.rajpopat
Jan 2 at 23:19

@javan.rajpopat they are stored as type DATE

– Eric
Jan 2 at 23:21

@javan.rajpopat they are stored as type DATE

– Eric
Jan 2 at 23:21

it should be in the data type DATETIME or TIMESTAMP to compare it by Time and Date.

– javan.rajpopat
Jan 2 at 23:23

it should be in the data type DATETIME or TIMESTAMP to compare it by Time and Date.

– javan.rajpopat
Jan 2 at 23:23

1

1

The issue is that the DATE literal is for calendar dates only, it does not take a time-of-day component. The TIMESTAMP literal would do what you need; do not remove the keyword DATE, but instead replace it with TIMESTAMP. This has one disadvantage though - it relies on implicit conversion between date and timestamp data types. It may be best to use TO_DATE() with the appropriate format model, in this case 'yyyy-mm-dd hh24:mi:ss'.

– mathguy
Jan 2 at 23:28

The issue is that the DATE literal is for calendar dates only, it does not take a time-of-day component. The TIMESTAMP literal would do what you need; do not remove the keyword DATE, but instead replace it with TIMESTAMP. This has one disadvantage though - it relies on implicit conversion between date and timestamp data types. It may be best to use TO_DATE() with the appropriate format model, in this case 'yyyy-mm-dd hh24:mi:ss'.

– mathguy
Jan 2 at 23:28

2

2

Question though - between a and b means >= a and <= b. If you use this for consecutive intervals, the endpoints will be double counted. The common (and correct) solution is to avoid the keyword between and to write the inequalities explicitly as >= a and < b - with strict inequality for the upper end point.

– mathguy
Jan 2 at 23:30

Question though - between a and b means >= a and <= b. If you use this for consecutive intervals, the endpoints will be double counted. The common (and correct) solution is to avoid the keyword between and to write the inequalities explicitly as >= a and < b - with strict inequality for the upper end point.

– mathguy
Jan 2 at 23:30

 | 
show 2 more comments

3 Answers
3

active

oldest

votes

3

Try to convert them explicitly with to_date().

WHERE h.creation_time >= to_date('2017-01-01', 'YYYY-MM-DD')

      AND h.closed_time BETWEEN to_date('2018-12-16 18:19:00', 'YYYY-MM-DD HH24:MI:SS')

                                AND to_date('2018-12-16 18:20:00', 'YYYY-MM-DD HH24:MI:SS')

share|improve this answer

answered Jan 2 at 23:23

sticky bitsticky bit

15.7k111733

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This seemed to be the best solution considering how much date I do have, thank you for your quick feedback!
  
  – Eric
  Jan 3 at 14:40
  

  
  
  
  

add a comment | 

2

A timestamp literal starts with the keyword TIMESTAMP (just as a date literal starts with DATE):

where h.creation_time >= date '2017-01-01'

and h.closed_time between timestamp '2018-12-16 18:19:00' and timestamp '2018-12-16 18:20:00'

In order to take fractions of seconds into account, you shouldn't use BETWEEN however, but:

where h.creation_time >= date '2017-01-01'

and h.closed_time >= timestamp '2018-12-16 18:19:00' 

and h.closed_time < timestamp '2018-12-16 18:20:00'

share|improve this answer

answered Jan 3 at 2:55

Thorsten KettnerThorsten Kettner

53k32643

add a comment | 

0

everything between 6:19pm and 6:20pm (one minute time frame)

The problem presented here is not only technical but lingual. The word "between" can have several subtle differences such as driving between the gateposts (i..e hopefully that car does not hit either side and so no gatepost is included) or flying between London and Paris where both cities are included otherwise you have fallen short of the destination.

SQL standards use the latter approach for between, it is defined as inclusive of both endpoints. This is a potential problem for date/time ranges as it can lead to double-counting of endpoints. As @mathguy indicates in his excellent comment under the question the stricter approach is to avoid using between and explicitly define the range this way:

WHERE h.creation_time >= to_date('2017-01-01', 'YYYY-MM-DD')

AND h.closed_time >= to_date('2018-12-16 18:19:00', 'YYYY-MM-DD HH24:MI:SS')

AND h.closed_time  < to_date('2018-12-16 18:20:00', 'YYYY-MM-DD HH24:MI:SS')

IF the closed_time column is a date data type use to_date()

IF the closed_time column is a timestamp data type use to_timestamp() instead.

share|improve this answer

edited Jan 3 at 0:39

answered Jan 3 at 0:30

Used_By_AlreadyUsed_By_Already

23.1k22139

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54014415%2fhow-do-include-time-with-my-sql-query-for-finding-data-between-two-dates%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

3 Answers
3

active

oldest

votes

3 Answers
3

active

oldest

votes

active

oldest

votes

active

oldest

votes

3

Try to convert them explicitly with to_date().

WHERE h.creation_time >= to_date('2017-01-01', 'YYYY-MM-DD')

      AND h.closed_time BETWEEN to_date('2018-12-16 18:19:00', 'YYYY-MM-DD HH24:MI:SS')

                                AND to_date('2018-12-16 18:20:00', 'YYYY-MM-DD HH24:MI:SS')

share|improve this answer

answered Jan 2 at 23:23

sticky bitsticky bit

15.7k111733

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This seemed to be the best solution considering how much date I do have, thank you for your quick feedback!
  
  – Eric
  Jan 3 at 14:40
  

  
  
  
  

add a comment | 

3

Try to convert them explicitly with to_date().

WHERE h.creation_time >= to_date('2017-01-01', 'YYYY-MM-DD')

      AND h.closed_time BETWEEN to_date('2018-12-16 18:19:00', 'YYYY-MM-DD HH24:MI:SS')

                                AND to_date('2018-12-16 18:20:00', 'YYYY-MM-DD HH24:MI:SS')

share|improve this answer

answered Jan 2 at 23:23

sticky bitsticky bit

15.7k111733

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This seemed to be the best solution considering how much date I do have, thank you for your quick feedback!
  
  – Eric
  Jan 3 at 14:40
  

  
  
  
  

add a comment | 

3

3

3

Try to convert them explicitly with to_date().

WHERE h.creation_time >= to_date('2017-01-01', 'YYYY-MM-DD')

      AND h.closed_time BETWEEN to_date('2018-12-16 18:19:00', 'YYYY-MM-DD HH24:MI:SS')

                                AND to_date('2018-12-16 18:20:00', 'YYYY-MM-DD HH24:MI:SS')

share|improve this answer

answered Jan 2 at 23:23

sticky bitsticky bit

15.7k111733

Try to convert them explicitly with to_date().

WHERE h.creation_time >= to_date('2017-01-01', 'YYYY-MM-DD')

      AND h.closed_time BETWEEN to_date('2018-12-16 18:19:00', 'YYYY-MM-DD HH24:MI:SS')

                                AND to_date('2018-12-16 18:20:00', 'YYYY-MM-DD HH24:MI:SS')

share|improve this answer

answered Jan 2 at 23:23

sticky bitsticky bit

15.7k111733

share|improve this answer

share|improve this answer

answered Jan 2 at 23:23

sticky bitsticky bit

15.7k111733

answered Jan 2 at 23:23

sticky bitsticky bit

15.7k111733

answered Jan 2 at 23:23

sticky bitsticky bit

15.7k111733

15.7k111733

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This seemed to be the best solution considering how much date I do have, thank you for your quick feedback!
  
  – Eric
  Jan 3 at 14:40
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This seemed to be the best solution considering how much date I do have, thank you for your quick feedback!
  
  – Eric
  Jan 3 at 14:40
  

  
  
  
  

This seemed to be the best solution considering how much date I do have, thank you for your quick feedback!

– Eric
Jan 3 at 14:40

This seemed to be the best solution considering how much date I do have, thank you for your quick feedback!

– Eric
Jan 3 at 14:40

add a comment | 

2

A timestamp literal starts with the keyword TIMESTAMP (just as a date literal starts with DATE):

where h.creation_time >= date '2017-01-01'

and h.closed_time between timestamp '2018-12-16 18:19:00' and timestamp '2018-12-16 18:20:00'

In order to take fractions of seconds into account, you shouldn't use BETWEEN however, but:

where h.creation_time >= date '2017-01-01'

and h.closed_time >= timestamp '2018-12-16 18:19:00' 

and h.closed_time < timestamp '2018-12-16 18:20:00'

share|improve this answer

answered Jan 3 at 2:55

Thorsten KettnerThorsten Kettner

53k32643

add a comment | 

2

A timestamp literal starts with the keyword TIMESTAMP (just as a date literal starts with DATE):

where h.creation_time >= date '2017-01-01'

and h.closed_time between timestamp '2018-12-16 18:19:00' and timestamp '2018-12-16 18:20:00'

In order to take fractions of seconds into account, you shouldn't use BETWEEN however, but:

where h.creation_time >= date '2017-01-01'

and h.closed_time >= timestamp '2018-12-16 18:19:00' 

and h.closed_time < timestamp '2018-12-16 18:20:00'

share|improve this answer

answered Jan 3 at 2:55

Thorsten KettnerThorsten Kettner

53k32643

add a comment | 

2

2

2

A timestamp literal starts with the keyword TIMESTAMP (just as a date literal starts with DATE):

where h.creation_time >= date '2017-01-01'

and h.closed_time between timestamp '2018-12-16 18:19:00' and timestamp '2018-12-16 18:20:00'

In order to take fractions of seconds into account, you shouldn't use BETWEEN however, but:

where h.creation_time >= date '2017-01-01'

and h.closed_time >= timestamp '2018-12-16 18:19:00' 

and h.closed_time < timestamp '2018-12-16 18:20:00'

share|improve this answer

answered Jan 3 at 2:55

Thorsten KettnerThorsten Kettner

53k32643

A timestamp literal starts with the keyword TIMESTAMP (just as a date literal starts with DATE):

where h.creation_time >= date '2017-01-01'

and h.closed_time between timestamp '2018-12-16 18:19:00' and timestamp '2018-12-16 18:20:00'

In order to take fractions of seconds into account, you shouldn't use BETWEEN however, but:

where h.creation_time >= date '2017-01-01'

and h.closed_time >= timestamp '2018-12-16 18:19:00' 

and h.closed_time < timestamp '2018-12-16 18:20:00'

share|improve this answer

answered Jan 3 at 2:55

Thorsten KettnerThorsten Kettner

53k32643

share|improve this answer

share|improve this answer

answered Jan 3 at 2:55

Thorsten KettnerThorsten Kettner

53k32643

answered Jan 3 at 2:55

Thorsten KettnerThorsten Kettner

53k32643

answered Jan 3 at 2:55

Thorsten KettnerThorsten Kettner

53k32643

53k32643

add a comment | 

add a comment | 

0

everything between 6:19pm and 6:20pm (one minute time frame)

The problem presented here is not only technical but lingual. The word "between" can have several subtle differences such as driving between the gateposts (i..e hopefully that car does not hit either side and so no gatepost is included) or flying between London and Paris where both cities are included otherwise you have fallen short of the destination.

SQL standards use the latter approach for between, it is defined as inclusive of both endpoints. This is a potential problem for date/time ranges as it can lead to double-counting of endpoints. As @mathguy indicates in his excellent comment under the question the stricter approach is to avoid using between and explicitly define the range this way:

WHERE h.creation_time >= to_date('2017-01-01', 'YYYY-MM-DD')

AND h.closed_time >= to_date('2018-12-16 18:19:00', 'YYYY-MM-DD HH24:MI:SS')

AND h.closed_time  < to_date('2018-12-16 18:20:00', 'YYYY-MM-DD HH24:MI:SS')

IF the closed_time column is a date data type use to_date()

IF the closed_time column is a timestamp data type use to_timestamp() instead.

share|improve this answer

edited Jan 3 at 0:39

answered Jan 3 at 0:30

Used_By_AlreadyUsed_By_Already

23.1k22139

add a comment | 

0

everything between 6:19pm and 6:20pm (one minute time frame)

The problem presented here is not only technical but lingual. The word "between" can have several subtle differences such as driving between the gateposts (i..e hopefully that car does not hit either side and so no gatepost is included) or flying between London and Paris where both cities are included otherwise you have fallen short of the destination.

SQL standards use the latter approach for between, it is defined as inclusive of both endpoints. This is a potential problem for date/time ranges as it can lead to double-counting of endpoints. As @mathguy indicates in his excellent comment under the question the stricter approach is to avoid using between and explicitly define the range this way:

WHERE h.creation_time >= to_date('2017-01-01', 'YYYY-MM-DD')

AND h.closed_time >= to_date('2018-12-16 18:19:00', 'YYYY-MM-DD HH24:MI:SS')

AND h.closed_time  < to_date('2018-12-16 18:20:00', 'YYYY-MM-DD HH24:MI:SS')

IF the closed_time column is a date data type use to_date()

IF the closed_time column is a timestamp data type use to_timestamp() instead.

share|improve this answer

edited Jan 3 at 0:39

answered Jan 3 at 0:30

Used_By_AlreadyUsed_By_Already

23.1k22139

add a comment | 

0

0

0

everything between 6:19pm and 6:20pm (one minute time frame)

The problem presented here is not only technical but lingual. The word "between" can have several subtle differences such as driving between the gateposts (i..e hopefully that car does not hit either side and so no gatepost is included) or flying between London and Paris where both cities are included otherwise you have fallen short of the destination.

SQL standards use the latter approach for between, it is defined as inclusive of both endpoints. This is a potential problem for date/time ranges as it can lead to double-counting of endpoints. As @mathguy indicates in his excellent comment under the question the stricter approach is to avoid using between and explicitly define the range this way:

WHERE h.creation_time >= to_date('2017-01-01', 'YYYY-MM-DD')

AND h.closed_time >= to_date('2018-12-16 18:19:00', 'YYYY-MM-DD HH24:MI:SS')

AND h.closed_time  < to_date('2018-12-16 18:20:00', 'YYYY-MM-DD HH24:MI:SS')

IF the closed_time column is a date data type use to_date()

IF the closed_time column is a timestamp data type use to_timestamp() instead.

share|improve this answer

edited Jan 3 at 0:39

answered Jan 3 at 0:30

Used_By_AlreadyUsed_By_Already

23.1k22139

everything between 6:19pm and 6:20pm (one minute time frame)

The problem presented here is not only technical but lingual. The word "between" can have several subtle differences such as driving between the gateposts (i..e hopefully that car does not hit either side and so no gatepost is included) or flying between London and Paris where both cities are included otherwise you have fallen short of the destination.

SQL standards use the latter approach for between, it is defined as inclusive of both endpoints. This is a potential problem for date/time ranges as it can lead to double-counting of endpoints. As @mathguy indicates in his excellent comment under the question the stricter approach is to avoid using between and explicitly define the range this way:

WHERE h.creation_time >= to_date('2017-01-01', 'YYYY-MM-DD')

AND h.closed_time >= to_date('2018-12-16 18:19:00', 'YYYY-MM-DD HH24:MI:SS')

AND h.closed_time  < to_date('2018-12-16 18:20:00', 'YYYY-MM-DD HH24:MI:SS')

IF the closed_time column is a date data type use to_date()

IF the closed_time column is a timestamp data type use to_timestamp() instead.

share|improve this answer

edited Jan 3 at 0:39

answered Jan 3 at 0:30

Used_By_AlreadyUsed_By_Already

23.1k22139

share|improve this answer

share|improve this answer

edited Jan 3 at 0:39

edited Jan 3 at 0:39

edited Jan 3 at 0:39

answered Jan 3 at 0:30

Used_By_AlreadyUsed_By_Already

23.1k22139

answered Jan 3 at 0:30

Used_By_AlreadyUsed_By_Already

23.1k22139

answered Jan 3 at 0:30

Used_By_AlreadyUsed_By_Already

23.1k22139

23.1k22139

add a comment | 

add a comment | 

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54014415%2fhow-do-include-time-with-my-sql-query-for-finding-data-between-two-dates%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

This page is only for reference, If you need detailed information, please check here