how to get records basing on start date and end

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to get records basing on start date and end

Author

Topic

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-04-10 : 07:35:43

hello all,

in my query i am getting records of starting date and before end date

for example from 6th april to 10 th april records i am getting records of 6th to 9th april only i have wriiten in dynamic query


	IF	(@dt_VisitedStartDate IS NOT NULL and @dt_VisitedEndDate IS NOT NULL)	
	BEGIN						
	SET	@vc_SQL = @vc_SQL +'  '+' AND CDV.CreatedDate BETWEEN ''' + CAST(CONVERT(date,@dt_VisitedStartDate,101) AS VARCHAR) + ''' AND ''' + CAST(CONVERT(date,@dt_VisitedEndDate,101) AS VARCHAR)+ ''' ' 
	
	END

[COde]
SET @vc_SQL = @vc_SQL + ' '+'AND CAST(CONVERT(date,CDV.CreatedDate,101) AS VARCHAR) >= ''' + CAST(CONVERT(date,@dt_VisitedStartDate,101) AS VARCHAR)+ ''' '
SET @vc_SQL = @vc_SQL + ' '+' AND CAST(CONVERT(date,CDV.CreatedDate,101) AS VARCHAR) <= ''' + CAST(CONVERT(date,@dt_VisitedEndDate,101) AS VARCHAR) + ''' '
[/code]

P.V.P.MOhan

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-10 : 07:40:58

Does the date field column in your table is storing the time part too?

Cheers
MIK

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-10 : 07:49:55

If it does you'll need to change the below code as per your requirements..

And that per your requirements, the "between" won't work. I think you'll need to have >= and "<" (not <=) in your query.

Declare @dt_VisitedStartDate datetime,@dt_VisitedEndDate datetime, @vc_SQL varchar(8000)
SET @dt_VisitedStartDate = '20130406'
SET @dt_VisitedEndDate = '20130410'

SET @vc_SQL = 'CONVERT(date,CDV.CreatedDate,101) >= ''' + Cast(CONVERT(date,@dt_VisitedStartDate,101) as Varchar)+ ''' '+
'CONVERT(date,CDV.CreatedDate,101) < ''' + CAst(CONVERT(date,@dt_VisitedEndDate,101) as Varchar)+''''

Print @vc_SQL

Cheers
MIK

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-10 : 08:20:41

http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-04-10 : 08:32:10

one thing is running in my mind i am not getting end date records so if i add one date to my end date then i can get expected output here is my dynamic query :


	IF	(@dt_VisitedStartDate IS NOT NULL and @dt_VisitedEndDate IS NOT NULL)	
	BEGIN						
	SET	@vc_SQL = @vc_SQL +'  '+' AND CDV.CreatedDate BETWEEN ''' + CAST(CONVERT(date,@dt_VisitedStartDate,101) AS VARCHAR) + ''' AND ''' + CAST(CONVERT(date,@dt_VisitedEndDate,101) AS VARCHAR)+ ''' ' 
	
	END

so now how to add one day in this dynamic query using DATEADD

P.V.P.MOhan

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-04-10 : 08:42:18

and i added the MIK_2008 posted in my requirement and it is giving the entire records even though i am specifying dates

P.V.P.MOhan

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-10 : 08:42:19

that means your CreatedDate has time portion as well and you're passing Start/End dates parameter's values as Date(without time portion). Try and check if replacing

AND CDV.CreatedDate BETWEEN
with
AND convert(date,CDV.CreatedDate) BETWEEN

works for you?

Cheers
MIK

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-10 : 08:46:03

quote:
Originally posted by mohan123

and i added the MIK_2008 posted in my requirement and it is giving the entire records even though i am specifying dates

P.V.P.MOhan

by all records you mean, outside of the start and end date boundries?

Cheers
MIK

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-04-10 : 09:03:14

@dt_VisitedStartDate= '2013-04-09',@dt_VisitedEndDate ='2013-04-10' then it need to show between records in both the dates but it is giving all the records.....by adding the above query has mik_2008 has written

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-10 : 09:05:08

SET DATEFORMAT YMD
GO
-- Run Your Query

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-10 : 09:09:26

quote:
Originally posted by mohan123

one thing is running in my mind i am not getting end date records so if i add one date to my end date then i can get expected output here is my dynamic query :
so now how to add one day in this dynamic query using DATEADD
P.V.P.MOhan

IF (YoursConditions)
BEGIN
SET @dt_VisitedEndDate = DATEADD( DD, 1, @dt_VisitedEndDate)
SET @vc_SQL = ..................
END

EDIT: what is the datatype for @vc_SQL variable?
--
Chandu

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-10 : 12:32:26

quote:
Originally posted by mohan123

@dt_VisitedStartDate= '2013-04-09',@dt_VisitedEndDate ='2013-04-10' then it need to show between records in both the dates but it is giving all the records.....by adding the above query has mik_2008 has written

P.V.P.MOhan

Can you provide the resulting dynamic query (formed as a result of @vc_SQL)? e.g. instead of executing @vc_SQL, print it and paste it's output here ..specifically the FROM clauses and WHERE conditions. Take a look at the example I posted earlier for you, where I am printing the value of @vc_SQL.

Cheers
MIK

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-04-12 : 05:11:16

time portions said by MIK2008 implemented in my query worked fine for me ( convert(date,CDV.CreatedDate) BETWEEN)

thanks MIk

P.V.P.MOhan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources