Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


select statement


select statement

Author
Message
BeginnerBug
BeginnerBug
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 350
i have a table as follows...

-------------------------------
From date | To _date
-------------------------------
10-jun-2010 | 30-jun-2010
20-jun-2010 | 30-aug-2010
03-aug-2009 | 30-aug-2010
--------------------------------

i have @pd_from_date='1-jun-2010' and @pd_to_date='31-aug-2010'

i have a flag @lb_error_flag bit... it should be 1 if date from table not between @pd_from_date and @pd_to_date

if all date in the tbl between @pd_from_date and @pd_to_date then it should be 0...

DECLARE @lb_error_flag BIT

SET @lb_error_flag=0

SELECT @lb_error_flag=1 FROM Trp_table WHERE CONVERT(VARCHAR(11), LTRIM(RTRIM(From_date)), 106) < @pd_from_date AND CONVERT(VARCHAR(11), LTRIM(RTRIM(To _date)), 106) > @pd_to_date

_date


am i right?... or any other better approach?...
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8973 Visits: 19024
What datatype are the 'date' columns in your table?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
BeginnerBug
BeginnerBug
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 350
hai chris..


both are varchar types.... specification is like tht where i cant change that as datetime data type...
BeginnerBug
BeginnerBug
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 350
then how to convert a varchar type variable into datetime type?..
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8973 Visits: 19024
MonsterRocks (10/21/2010)
then how to convert a varchar type variable into datetime type?..


Look up CONVERT in BOL, and choose the 'Style' which matches your text date. 106 looks promising even though the date part delimiter is different:

SELECT CONVERT(DATETIME, '10-jun-2010', 106)

Check that this works against your data, then create a sample data script something like this:

CREATE TABLE #table1 (ID INT, meat VARCHAR(25))
INSERT INTO #table1 (ID, meat)
SELECT 1, 'beef pork' UNION ALL
SELECT 2, 'pork chicken' UNION ALL
SELECT 3, 'pork chicken beef'

and post it here for reference.

Your query will look something like this:

SELECT Fromdate, Todate,
Errors = CASE WHEN (Fromdate >= @pd_from_date and Fromdate <= @pd_to_date)
AND (Todate >= @pd_from_date and Todate <= @pd_to_date) THEN 1 ELSE 0 END
FROM MyTable

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
BeginnerBug
BeginnerBug
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 350
create table trp_table (from_date varchar(15),to_date varchar(15))
insert into trp_table(from_date,to_date)
select CONVERT(VARCHAR(11), LTRIM(RTRIM('01-JUL-2010')), 106), CONVERT(VARCHAR(11), LTRIM(RTRIM('22-JUL-2010')))
UNION ALL
SELECT CONVERT(VARCHAR(11), LTRIM(RTRIM('15-JUL-2010')), 106), CONVERT(VARCHAR(11), LTRIM(RTRIM('20-JUL-2010')))
UNION ALL
SELECT CONVERT(VARCHAR(11), LTRIM(RTRIM('11-FEB-2010')), 106), CONVERT(VARCHAR(11), LTRIM(RTRIM('27-JUL-2010')))


DECLARE @lb_error_flag BIT

SELECT @lb_error_flag = CASE WHEN (from_date >= CONVERT(VARCHAR(11), LTRIM(RTRIM( '01-JUN-2010')), 106) and from_date <= CONVERT(VARCHAR(11), LTRIM(RTRIM( '30-JUl-2010')), 106) )
AND (to_date >= CONVERT(VARCHAR(11), LTRIM(RTRIM( '01-JUN-2010')), 106) and to_date <= CONVERT(VARCHAR(11), LTRIM(RTRIM( '30-JUN-2010')), 106)) THEN 1 ELSE 0 END
FROM trp_table
print @lb_error_flag



Still it returns 1... where it should be 0.. 3rd row differ from the from_date and to_date.....

and select select CONVERT(VARCHAR(11), LTRIM(RTRIM('01-JUL-2010')), 106) works in my environment....
select isdate(CONVERT(VARCHAR(11), LTRIM(RTRIM('01-JUL-2010')), 106)) returns 1
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8973 Visits: 19024
Why do you think the query should return only one row (or none) regardless of how many rows may or may not match the filter?

I'm not sure if this statement

CONVERT(VARCHAR(11), LTRIM(RTRIM('01-JUL-2010')), 106)

actually does anything at all. Try this:

SELECT CONVERT(VARCHAR(11), LTRIM(RTRIM('41-JUL-2010')), 106).

The string dates cannot be sensibly compared as they stand. They must be converted to proper dates for SQL Server to be able to compare them properly. Both the columns and the variables. Here's some stuff for you to play with:

create table #trp_table (from_date varchar(15),to_date varchar(15))
insert into #trp_table(from_date,to_date)
SELECT '01-JUL-2010', '22-JUL-2010' UNION ALL
SELECT '15-JUL-2010', '20-JUL-2010' UNION ALL
SELECT '11-FEB-2010', '27-JUL-2010'

DECLARE
@To_Date varchar(15), @from_date varchar(15),
@To_DateDT DATETIME, @from_dateDT DATETIME

SET @from_date = '01-JUN-2010'
SET @To_Date = '30-JUN-2010'

SET @from_dateDT = CONVERT(DATETIME, @From_Date, 106)
SET @To_DateDT = CONVERT(DATETIME, @To_Date, 106)

-- check variables are good:
SELECT FromDate = @from_dateDT, ToDate = @To_DateDT

-- check date ranges:
SELECT From_date, To_date,
Errors = CASE WHEN (From_date >= @from_dateDT AND From_date <= @To_DateDT)
AND (To_date >= @from_dateDT AND To_date <= @To_DateDT) THEN 1 ELSE 0 END
FROM ( -- convert string to datetime in this inner query or derived table
SELECT
From_date = CONVERT(DATETIME, From_Date, 106),
To_date = CONVERT(DATETIME, To_Date, 106)
FROM #trp_table
) d



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
BeginnerBug
BeginnerBug
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 350
Great!!!!!!!!!!!!!... Working fine... Thanks chris....Thanks a lot...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search