SQL QUERY Date range string issue

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

SQL Server 2008 Forums

Transact-SQL (2008)

SQL QUERY Date range string issue

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-01-12 : 11:30:24

I am using the following date range condition in my Dynamic SQL string SP:
I am having problem with teh date range, it is getting the result but ignoring the year.
If i search for records between jan 01 2011 to jan 11 2011.

it is getting results from past year 2010 jan 1 2010 to jan 11 2010.

@Datefrom and DateTo both are datetime datatypes.


select @SQLWHERE = @SQLWHERE + ' and CONVERT(varchar(10), A.ReceivedDate, 101)  between ''' + CONVERT(varchar(10), @DateFrom, 101) + ''' and ''' + CONVERT(varchar(10), @DateTo, 101) + ''''

thank you very much for the helpful info.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-12 : 11:35:04

please dont convert dates to varchar. it will cause it to return wrong results.
what you want is this?

select @SQLWHERE = @SQLWHERE + ' and A.ReceivedDate>= ''' + CONVERT(varchar(10), @DateFrom, 101) + ''' and A.ReceivedDate< DATEADD(dd,1,''' + CONVERT(varchar(10), @DateTo, 101) + ''')'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-12 : 12:39:56

I agree with Visakh on the casting of dates to VARCHAR for comparison. Also, I'd add that you should also use an ISO date format for your dynamic SQL. It looks like you are trying to remove the Time portion. If so then you should use the 112 format. For example:

select @SQLWHERE = @SQLWHERE + ' and A.ReceivedDate between ''' + CONVERT(varchar(10), @DateFrom, 112) + ''' and ''' + CONVERT(varchar(10), @DateTo, 112) + ''''

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-01-12 : 16:02:33

Visakh & lamprey, Thank you very much for the helpful info.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-13 : 11:01:23

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-13 : 13:48:50

Good SQL Programmers avoid dynamic SQL. It says the problem is so poorly defined that a user has to figure it out on the fly at runtime.

You confuse rows with records, and fail to use ISO-8601 temporal formats or DATE datat types. You are still writing 1950's COBOL or 1960's BASIC, but you are doing it in SQL. Think in abstract data tyeps and not pictures.

Let's use ISO-11179 names and soem guess about the DDL you did not post:

CREATE PROCEDURE GetVagueStuff
(@in_report_start_date DATE,
@in_report_end_date DATE)
BEGIN
SELECT ..
FROM A_Stuff AS A, ..
WHERE A.receipt_date BETWEEN @in_report_start_date AND
@in_report_end_date;
..
END;

you might want to use CAST (A.receipt_date AS DATE) if it is still DATETIME.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources