Improve company productivity with a Business Account.Sign Up

x
?
Solved

select date between startdate and enddate return no row :S

Posted on 2012-12-26
10
Medium Priority
?
492 Views
Last Modified: 2013-01-09
i have two table ..
SELECT TOP 1000 [ProgramID]
      ,[ProgramName]
      ,[ProgramStart]
      ,[ProgramEnd]
      ,[ProgramWeekDay]
  FROM [radiomix].[dbo].[MixFmProgram]

Open in new window



ProgramID	ProgramName	ProgramStart	ProgramEnd	ProgramWeekDay
63	¿¿ ¿¿¿¿¿¿	12:00AM	1:00AM	WE
64	¿¿¿¿¿¿ ¿¿¿¿¿¿ General Music	1:00AM	4:00AM	WE
65	¿¿¿¿¿ ¿¿¿¿ Religious 	4:00AM	4:30AM	WE
66	¿¿¿¿¿¿ ¿¿¿¿¿¿ General Music	4:30AM	6:00AM	WE
67	¿¿¿¿¿¿	6:00AM	10:00AM	WE
68	¿¿¿¿¿ ¿¿¿¿	10:00AM	11:00AM	WE
69	¿¿¿¿ ¿¿¿¿	11:00AM	1:00PM	WE
70	¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿	1:00PM	2:00PM	WE
71	¿¿¿ ¿¿¿¿¿	2:00PM	4:00PM	WE

Open in new window



SELECT TOP 1000 [id]
      ,[datetimein]
      ,[msisdn]
      ,[reqport]
      ,[message]
      ,[DAY]
      ,[ProgramIDM]
  FROM [radiomix].[dbo].[chat]

Open in new window



id	datetimein	msisdn	reqport	message	DAY	ProgramIDM
381996	2012-12-26 16:25:43.043	966568946632	630590	¿¿¿¿ ¿¿¿¿ ¿¿ ¿¿¿¿¿¿ ¿¿¿¿¿ ¿¿¿¿¿ ¿¿¿¿¿¿ ¿¿¿ ¿¿¿¿¿  0568946632	WE	0
381995	2012-12-26 16:24:00.483	966568946632	630590	¿¿¿¿ ¿¿¿¿ ¿¿ ¿¿¿¿¿¿ ¿¿¿¿¿ ¿¿¿¿¿ ¿¿¿¿¿¿ ¿¿¿ ¿¿¿¿¿  0568946632	WE	0

Open in new window




my query to select the program name :

SELECT a.*
	  ,p.*
	  ,RIGHT(CONVERT(VARCHAR,[datetimein],100),7)
  FROM [radiomix].[dbo].[chat] a
 inner JOIN [radiomix].[dbo].[MixFmProgram] p ON (a.DAY = p.ProgramWeekDay)
   WHERE a.ID = '381963'
 AND RIGHT(CONVERT(VARCHAR,a.datetimein,100),7) > p.ProgramStart 
  and RIGHT(CONVERT(VARCHAR,a.datetimein,100),7) < p.ProgramEnd

Open in new window


result no  record :S


why ?? if i remove the restriction..
i get all possibility :


ProgramStart	ProgramEnd	ProgramWeekDay	msgdatetime
12:00AM	1:00AM	WE	 3:58PM
1:00AM	4:00AM	WE	 3:58PM
4:00AM	4:30AM	WE	 3:58PM
4:30AM	6:00AM	WE	 3:58PM
6:00AM	10:00AM	WE	 3:58PM
10:00AM	11:00AM	WE	 3:58PM
11:00AM	1:00PM	WE	 3:58PM
1:00PM	2:00PM	WE	 3:58PM
2:00PM	4:00PM	WE	 3:58PM
4:00PM	6:00PM	WE	 3:58PM
6:00PM	7:00PM	WE	 3:58PM
7:00PM	8:00PM	WE	 3:58PM
8:00PM	9:00PM	WE	 3:58PM
9:00PM	10:00PM	WE	 3:58PM
10:00PM	10:30PM	WE	 3:58PM
10:30PM	12:00AM	WE	 3:58PM

Open in new window

0
Comment
Question by:AFIF JABADO
  • 5
  • 4
10 Comments
 
LVL 8

Expert Comment

by:hpdvs2
ID: 38721149
Is ProgramStart and ProgramEnd just varchars?  Because your comparing Greater / Less than with strings, not dates.


>>>  RIGHT(CONVERT(VARCHAR,a.datetimein,100),7) < p.ProgramEnd  <<<


try this: CAST(...) AS TIME, so this:


SELECT a.*
        ,p.*
        ,RIGHT(CONVERT(VARCHAR,[datetimein],100),7)
  FROM [radiomix].[dbo].[chat] a
 inner JOIN [radiomix].[dbo].[MixFmProgram] p ON (a.DAY = p.ProgramWeekDay)
   WHERE a.ID = '381963'
 AND CAST(RIGHT(CONVERT(VARCHAR,a.datetimein,100),7)) AS TIME > CAST(p.ProgramStart) AS TIME
  and CAST(RIGHT(CONVERT(VARCHAR,a.datetimein,100),7)) AS TIME < CAST(p.ProgramEnd) AS TIME
0
 
LVL 1

Author Comment

by:AFIF JABADO
ID: 38721222
Msg 1035, Level 15, State 10, Line 7
Incorrect syntax near 'CAST', expected 'AS'.
0
 
LVL 1

Author Comment

by:AFIF JABADO
ID: 38721223
the format of programstart and programend is like 1:00Am
and datetimein is a datetime i should get the time only and compare

and i have already have two field in each tables to check weekday !
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38721320
the format of programstart and programend is like 1:00Am
If you treat that as a string then 9:00AM > 11:00AM  or 9:00AM > 2:00PM Is that really what you want?
0
 
LVL 1

Author Comment

by:AFIF JABADO
ID: 38721327
yes i have write this query if we can simplify it .. on convert and like this
the data stored in programstart is varchar type with format 1:00am 2:00pm

and i have a datetimein in another table with type datetime i should take the part time and compare
SELECT top 2 a.id,p.ProgramID,a.ProgramIDM
  FROM [radiomix].[dbo].[chat] a
 inner JOIN [radiomix].[dbo].[MixFmProgram] p ON (a.DAY = p.ProgramWeekDay)
   WHERE 
  CONVERT(varchar,[datetimein], 114) >= convert(varchar, CONVERT(datetime,p.ProgramStart), 114)
and CONVERT(varchar,[datetimein], 114) < convert(varchar, CONVERT(datetime,p.ProgramEnd), 114 )
 order by a.id desc

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38721334
If you are just trying to compare times than using a style of 114 should be acceptable for comparisons.
0
 
LVL 1

Author Comment

by:AFIF JABADO
ID: 38721341
if you can tell me how i can convert a varchar 2:00pm to hh:mm:ss 24hr
and same the datetime to a var char hh:mm:ss 24hr
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 38721375
if you can tell me how i can convert a varchar 2:00pm to hh:mm:ss 24hr
You can convert 2:00pm to either time or datetime as follows:
CAST('02:00pm' AS time)
CAST('02:00pm' AS datetime)
0
 
LVL 1

Author Comment

by:AFIF JABADO
ID: 38721382
select  CAST('02:00pm' AS time)
 CAST('02:00pm' AS datetime)

 Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '02:00pm'.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38722528
You do realize you are missing a comma, right?  As in:
select  CAST('02:00pm' AS time) ,
 CAST('02:00pm' AS datetime)
0

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

595 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question