Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.
Course Of The Month
8 days, 23 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
select date between startdate and enddate return no row :S
Posted on 2012-12-26
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 WE
SELECT 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 0
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
5
4
10 Comments
Is ProgramStart and ProgramEnd just varchars? Because your comparing Greater / Less than with strings, not dates.
>>> 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
>>> 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
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 !
Active 1 day ago
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?
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
Active 1 day ago
If you are just trying to compare times than using a style of 114 should be acceptable for comparisons.
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
Active 1 day ago
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)
You can convert 2:00pm to either time or datetime as follows:
CAST('02:00pm' AS time)
CAST('02:00pm' AS datetime)
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'.
Featured Post
Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| Where clause to fliter varchar with Characters | 12 | 52 | |
| Error "Could not load mobile report" in SQL SSRS 2016 after SP1 CU2 Update | 2 | 33 | |
| database audit for object access | 6 | 36 | |
| invoke-sqlcmd help | 5 | 25 |
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables.
Make a table:
Update a specific column given a specific row using the UPDATE statement:
Remove a set of values using the DELETE s…
Suggested Courses
Course of the Month8 days, 23 hours left to enroll
738 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.