Category: SQL Server

13 Jun 2018 08:41

Joined:	25/04/2007
Level:	Gold
Points:	408

Points:	5

Getting date range data

I have a column TransationDate is decimal data type the data's like 20170121 I want to display data's in date range, The database columns data like 20170121 ..............20170129. I want to display data's these date range based on given strartdate,enddate as criteria. In my query it is displayed only one record. what's wrong my query could any body advise to me.

declare @StartDate varchar(10)
declare @EndDate varchar(10)
set @StartDate ='20170121'
set @EndDate ='20170129'

SELECT *
FROM LCOM.dbo.PORCPAH PORCPAH
WHERE CONVERT(DATETIME, CONVERT(VARCHAR(8), PORCPAH.TRANSDATE), 112) >= @StartDate
AND CONVERT(DATETIME, CONVERT(VARCHAR(8), PORCPAH.TRANSDATE), 112) <= @EndDate

#769602

13 Jun 2018 13:29

Joined:	08/10/2009
Level:	Gold
Points:	14808

Points:	2

Can you get us the table definition and insert script for the data in that table. We can figure out why it is returning only one row as result.

Please mark this as Answer, if this helps

Regards,
Alwyn Duraisingh.M
<< Database Administrator >>
Jesus saves! The rest of us better make backups...

#769620

12 Jul 2018 17:40

Joined:	13/01/2015
Level:	Silver
Points:	230

Points:	2

Brite,

You need to parse your @start and @endDate values to Datetime as well, the same way you are casting it in left hand side of condition..

Thanks!

Thanks!
Anjali Bansal

~Give your best and lead the world

#769649

31 Jul 2018 15:02

Joined:	28/09/2005
Level:	Gold
Points:	637

Points:	3

I Hope, table has created with varchar for the date columns. so, try to check with below query.

SELECT *
FROM LCOM.dbo.PORCPAH PORCPAH
WHERE CONVERT(VARCHAR(8), PORCPAH.TRANSDATE), 112) >= CONVERT(VarChar(8), @StartDate, 112)
AND CONVERT(VARCHAR(8), PORCPAH.TRANSDATE), 112) <= CONVERT(VarChar(8), @StartDate, 112)

Regards,
Ramana Reddy

Thanks!
B.Ramana Reddy

Getting date range data

About dotnetspider.com

Quick Links

General

Services