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
why ?? if i remove the restriction..
i get all possibility :
ProgramStart ProgramEnd ProgramWeekDay msgdatetime12:00AM 1:00AM WE 3:58PM1:00AM 4:00AM WE 3:58PM4:00AM 4:30AM WE 3:58PM4:30AM 6:00AM WE 3:58PM6:00AM 10:00AM WE 3:58PM10:00AM 11:00AM WE 3:58PM11:00AM 1:00PM WE 3:58PM1:00PM 2:00PM WE 3:58PM2:00PM 4:00PM WE 3:58PM4:00PM 6:00PM WE 3:58PM6:00PM 7:00PM WE 3:58PM7:00PM 8:00PM WE 3:58PM8:00PM 9:00PM WE 3:58PM9:00PM 10:00PM WE 3:58PM10:00PM 10:30PM WE 3:58PM10:30PM 12:00AM WE 3:58PM
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
AFIF JABBADO
ASKER
Msg 1035, Level 15, State 10, Line 7
Incorrect syntax near 'CAST', expected 'AS'.
AFIF JABBADO
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 !
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?
AFIF JABBADO
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
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
>>> 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