How to search record from date time column to only date wise

Question

0.00/5 (No votes)

See more:

SELECT DISTINCT LOGINTIME ,ER.UserId,ER.CompanyName,ER.EmailId,ER.RegistrationNumber,SM.Name
FROM ExporterRegistration ER
Inner Join IPTRACK IP on ER.UserId=IP.[USER] INNER JOIN [Locations].[StatesMaster]
SM on SM.StateId=ER.StateId
WHERE IP.LOGINTIME BETWEEN '2017-01-01' AND '2017-12-31'
AND IP.USERTYPE='Exporter' And IP.[Status]='Y'

above is my Query --
OutPut---
LOGINTIME UserId CompanyName EmailId RegistrationNumber Name
2017-03-29 17:00:48.000 1 M/s. Fauna International faunainternationalhr@gmail.com TN2/ME/033/04 Tamilnadu
2017-03-29 17:29:02.000 1 M/s. Fauna International faunainternationalhr@gmail.com TN2/ME/033/04 Tamilnadu
2017-03-30 10:22:46.000 1 M/s. Fauna International faunainternationalhr@gmail.com TN2/ME/033/04 Tamilnadu
2017-03-30 11:16:23.000 1 M/s. Fauna International faunainternationalhr@gmail.com TN2/ME/033/04 Tamilnadu
2017-03-30 11:25:26.000 1 M/s. Fauna International faunainternationalhr@gmail.com TN2/ME/033/04 Tamilnadu
2017-03-30 11:30:16.000 1 M/s. Fauna International faunainternationalhr@gmail.com TN2/ME/033/04 Tamilnadu
2017-03-30 14:32:13.000 1206 afzal kader and company afzalkader@libertyfoods.ina NULL Andhra Pradesh
2017-03-30 14:40:31.000 771 Kalyan Aqua & Marine Exports India P Ltd kalyanaquafarms@gmail.com AP1/MT/143/12 Andhra Pradesh
2017-03-30 14:53:16.000 771 Kalyan Aqua & Marine Exports India P Ltd kalyanaquafarms@gmail.com AP1/MT/143/12 Andhra Pradesh
2017-03-30 14:56:49.000 543 Kader Exports Pvt. Ltd. afzalkader@libertyfoods.in MA1/MT/009/05 Maharashtra

What I have tried:

i Want to Find Data from this Date 2017-03-29 17:00:48.000 for Select Only Row
not select Multiple Row like it Will not Find Row from Date and Time
it Will Select Data For Only Date

so Please Help Me-

Posted 1-Jan-18 18:22pm

Member 12183079

Updated 1-Jan-18 19:46pm

Add a Solution

2 solutions

Solution 1

I created some sample data with just a time column instead of trying to replicate your data set.

Basically in your WHERE clause, in your BETWEEN statement you need to cast your start/end values as date time in order to take into account the date and time within your where clause. Also, in your sample query you aren't taking into account time in your where clause, you are only taking into account the date.

In the sample code below i've included some sample queries, including the requested scenario, so hopefully this makes what you need to do more clear.

SQL

DECLARE @TestData TABLE (
	Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	LoginTime DATETIME NULL
);

INSERT INTO @TestData ( LoginTime ) VALUES  ( '2017-03-29 17:00:48.000' )
INSERT INTO @TestData ( LoginTime ) VALUES  ( '2017-03-29 17:29:02.000' )
INSERT INTO @TestData ( LoginTime ) VALUES  ( '2017-03-30 10:22:46.000' )
INSERT INTO @TestData ( LoginTime ) VALUES  ( '2017-03-30 11:16:23.000' )
INSERT INTO @TestData ( LoginTime ) VALUES  ( '2017-03-30 11:25:26.000' )
INSERT INTO @TestData ( LoginTime ) VALUES  ( '2017-03-30 11:30:16.000' )
INSERT INTO @TestData ( LoginTime ) VALUES  ( '2017-03-30 14:32:13.000' )
INSERT INTO @TestData ( LoginTime ) VALUES  ( '2017-03-30 14:40:31.000' )
INSERT INTO @TestData ( LoginTime ) VALUES  ( '2017-03-30 14:53:16.000' )

--Select exact match where start/end values are same
SELECT * FROM @TestData WHERE LoginTime BETWEEN CAST('2017-03-29 17:00:48.000' AS DATETIME) AND CAST('2017-03-29 17:00:48.000' AS DATETIME)

--Or select exact match by making login time = param
SELECT * FROM @TestData WHERE LoginTime = CAST('2017-03-29 17:00:48.000' AS DATETIME)

--Should onl ypull 5 records by time of the date
SELECT * FROM @TestData WHERE LoginTime BETWEEN CAST('2017-03-29 17:29:02.000' AS DATETIME) AND CAST('2017-03-30 11:25:26.000' AS DATETIME)

--Ignores time, only pulls by date
SELECT * FROM @TestData WHERE CAST(LoginTime AS DATE) BETWEEN CAST('2017-03-29 17:29:02.000' AS DATE) AND CAST('2017-03-30 11:25:26.000' AS DATE)

Posted 1-Jan-18 18:49pm

David_Wimbley

Comments

Member 12183079 2-Jan-18 1:35am

this is good but i want to only two record
Like ony Date Wise
1-2017-03-29
2-2017-03-30

then how to do

David_Wimbley 2-Jan-18 1:43am

You need to clarify what you are wanting. By just saying you want 2 records with date 3/29 and 3/30 it appears you want to arbitrarily pull the data by selecting 2 random records where one date is 3/29 and the other date is 3/30 and that doesn't make sense.

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)

Pradeep Arthanari · Accepted Answer · 2018-01-01T19:46:00

You may try this..

If you apply distinct on a datetime part it will return all the rows. So its better to convert/cast into a date.

declare @LoginTable table (LOGINTIME DATETIME,UserId int,CompanyName varchar(100),EmailId varchar(50),
RegistrationNumber varchar(50),Name Varchar(50))

INSERT INTO @LoginTable VALUES('2017-03-29 17:00:48.000',1,'M/s. Fauna International','faunainternationalhr@gmail.com','TN2/ME/033/04','Tamilnadu')
INSERT INTO @LoginTable VALUES('2017-03-29 17:29:02.000',1,'M/s. Fauna International','faunainternationalhr@gmail.com','TN2/ME/033/04','Tamilnadu')
INSERT INTO @LoginTable VALUES('2017-03-30 10:22:46.000',1,'M/s. Fauna International','faunainternationalhr@gmail.com','TN2/ME/033/04','Tamilnadu')
INSERT INTO @LoginTable VALUES('2017-03-30 11:16:23.000',1,'M/s. Fauna International','faunainternationalhr@gmail.com','TN2/ME/033/04','Tamilnadu')
INSERT INTO @LoginTable VALUES('2017-03-30 11:25:26.000',1,'M/s. Fauna International','faunainternationalhr@gmail.com','TN2/ME/033/04','Tamilnadu')
INSERT INTO @LoginTable VALUES('2017-03-30 11:30:16.000',1,'M/s. Fauna International','faunainternationalhr@gmail.com','TN2/ME/033/04','Tamilnadu')
INSERT INTO @LoginTable VALUES('2017-03-30 14:32:13.000',	1206,'afzal kader and company','afzalkader@libertyfoods.ina',NULL,'Andhra Pradesh')
INSERT INTO @LoginTable VALUES('2017-03-30 14:40:31.000',	771	,'Kalyan Aqua & Marine Exports India P Ltd',' kalyanaquafarms@gmail.com','AP1/MT/143/12','Andhra Pradesh')
INSERT INTO @LoginTable VALUES('2017-03-30 14:53:16.000',	771	,'Kalyan Aqua & Marine Exports India P Ltd ','kalyanaquafarms@gmail.com',' AP1/MT/143/12','Andhra Pradesh')
INSERT INTO @LoginTable VALUES('2017-03-30 14:56:49.000',	543	,'Kader Exports Pvt. Ltd.','afzalkader@libertyfoods.in','MA1/MT/009/05','Maharashtra')

SELECT DISTINCT CAST(LOGINTIME AS DATE) AS distLoginDate,ER.UserId,ER.CompanyName,ER.EmailId,ER.RegistrationNumber,ER.Name
FROM @LoginTable ER 
WHERE ER.LOGINTIME BETWEEN '2017-01-01' AND '2017-12-31'
GROUP BY LOGINTIME, ER.UserId,ER.CompanyName,ER.EmailId,ER.RegistrationNumber,ER.Name