SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to get records basing on start date and end
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/10/2013 :  07:35:43  Show Profile  Reply with Quote
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) + ''' '

Edited by - mohan123 on 04/10/2013 07:36:21

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/10/2013 :  07:40:58  Show Profile  Reply with Quote
Does the date field column in your table is storing the time part too?

Cheers
MIK
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/10/2013 :  07:49:55  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 04/10/2013 :  08:20:41  Show Profile  Reply with Quote
http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/10/2013 :  08:32:10  Show Profile  Reply with Quote
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
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/10/2013 :  08:42:18  Show Profile  Reply with Quote
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
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/10/2013 :  08:42:19  Show Profile  Reply with Quote
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

Edited by - MIK_2008 on 04/10/2013 08:42:58
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/10/2013 :  08:46:03  Show Profile  Reply with Quote
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
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/10/2013 :  09:03:14  Show Profile  Reply with Quote
@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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 04/10/2013 :  09:05:08  Show Profile  Reply with Quote
SET DATEFORMAT YMD
GO
-- Run Your Query
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 04/10/2013 :  09:09:26  Show Profile  Reply with Quote
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

Edited by - bandi on 04/10/2013 09:22:09
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/10/2013 :  12:32:26  Show Profile  Reply with Quote
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
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/12/2013 :  05:11:16  Show Profile  Reply with Quote
time portions said by MIK2008 implemented in my query worked fine for me ( convert(date,CDV.CreatedDate) BETWEEN)

thanks MIk

P.V.P.MOhan
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000