select date between startdate and enddate return no row :S
i have two table ..
my query to select the program name :
result no record :S
why ?? if i remove the restriction..
i get all possibility :
SELECT TOP 1000 [ProgramID]
,[ProgramName]
,[ProgramStart]
,[ProgramEnd]
,[ProgramWeekDay]
FROM [radiomix].[dbo].[MixFmProgram]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 WESELECT TOP 1000 [id]
,[datetimein]
,[msisdn]
,[reqport]
,[message]
,[DAY]
,[ProgramIDM]
FROM [radiomix].[dbo].[chat]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 0my 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.ProgramEndresult 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:58PMASKER
Msg 1035, Level 15, State 10, Line 7
Incorrect syntax near 'CAST', expected 'AS'.
Incorrect syntax near 'CAST', expected 'AS'.
ASKER
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 !
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 !
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?
If you treat that as a string then 9:00AM > 11:00AM or 9:00AM > 2:00PM Is that really what you want?
ASKER
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
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
If you are just trying to compare times than using a style of 114 should be acceptable for comparisons.
ASKER
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
and same the datetime to a var char hh:mm:ss 24hr
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
select CAST('02:00pm' AS time)
CAST('02:00pm' AS datetime)
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '02:00pm'.
CAST('02:00pm' AS datetime)
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '02:00pm'.
You do realize you are missing a comma, right? As in:
select CAST('02:00pm' AS time) ,
CAST('02:00pm' AS datetime)
select CAST('02:00pm' AS time) ,
CAST('02:00pm' AS datetime)
>>> RIGHT(CONVERT(VARCHAR,a.da
try this: CAST(...) AS TIME, so this:
SELECT a.*
,p.*
,RIGHT(CONVERT(VARCHAR,[da
FROM [radiomix].[dbo].[chat] a
inner JOIN [radiomix].[dbo].[MixFmPro
WHERE a.ID = '381963'
AND CAST(RIGHT(CONVERT(VARCHAR
and CAST(RIGHT(CONVERT(VARCHAR