How can I select data between tow dates that are the same day in SQL server ?

Question

0.00/5 (No votes)

See more:

i want to find date time fromdate -> todate
if i write fromdate 03.12.2019 todate 03.12.2019 it doesn't give me any results but i have this date in my datebase .

i just one to display one date ?!
what should be the condition in this case ?

What I have tried:

DECLARE @fromdate datetime = Convert (datetime, '27.11.19', 4), @todate datetime= Convert (datetime, '27.11.19', 4)

Posted 2-Dec-19 23:48pm

Abdalla Ben Omran

Updated 3-Dec-19 4:55am

Add a Solution

Comments

phil.o 3-Dec-19 6:02am

You already got an answer to this question here[^]. You even accepted the solution.
So, how come you cannot apply today what you have learned yesterday?

[no name] 3-Dec-19 6:57am

Hi . it's another question or another problem . here i wanted to get data from the same day or same date

3 solutions

Solution 3

If this is going to be commonly used, you may want to create a User Defined Function to take care of this.

This example will return the last second of the date you entered

SQL

CREATE FUNCTION [dbo].[EndOfDay] (
  @DateText  Char(8)
)
RETURNS	DateTime
AS
BEGIN
  RETURN DateAdd(Ss, 86399, Convert (DateTime, @DateText, 4))
END
GO

You could then call this function using this standalone query

SQL

SELECT dbo.EndOfDay('27.11.19')

And in the query you provided you could use it in your declaration statement

SQL

DECLARE @fromdate datetime = Convert (datetime, '27.11.19', 4)
,       @todate   datetime = dbo.EndOfDay('27.11.19')

Posted 3-Dec-19 4:56am

MadMyche

Solution 1

Try to include time in your 2 date : 00:00:00 and 23:59:59

Posted 3-Dec-19 0:01am

Pascal-78

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2019-12-03T00:05:00

Either add 23:59:59.9999 to your ToDate, or

SELECT * FROM MyTable WHERE EnterDate BETWEEN @fromdate AND DATEADD(dd, 1, @todate)

The reason is that - certainly in my DB's - the EnterDate column is a timestamp - which means it includes a time portion. Since you don't supply a time portion to your from or to values, they default to midnight, so no "real" timestamps match them.
Adding one day to the to value sets that to midnight of the next day so the whole of 2019-03-12 matches.