November 5, 2018 at 6:16 pm
I have this problem How can I get rows in this example.
We are looking for crimes in our database on my Crimes table, where crimes were committed from March 2018 to August 2018 from 23:00 p.m. to 06:00 a.m.
of the next day. in that table we have a datetime field where the date and time are saved.
November 5, 2018 at 7:34 pm
marcelo salgado - Monday, November 5, 2018 6:16 PMI have this problem How can I get rows in this example.
We are looking for crimes in our database on my Crimes table, where crimes were committed from March 2018 to August 2018 from 23:00 p.m. to 06:00 a.m.
of the next day. in that table we have a datetime field where the date and time are saved.
Kindly post DDL with sample data and expected result.
Saravanan
November 6, 2018 at 6:33 am
marcelo salgado - Monday, November 5, 2018 6:16 PMI have this problem How can I get rows in this example.
We are looking for crimes in our database on my Crimes table, where crimes were committed from March 2018 to August 2018 from 23:00 p.m. to 06:00 a.m.
of the next day. in that table we have a datetime field where the date and time are saved.
SELECT *
FROM Crimes c
WHERE c.CrimeDate BETWEEN '20180301' AND '20180901'
AND (SUBSTRING(CONVERT(varchar,c.CrimeDate,121),12,5) >= '23:00'
OR SUBSTRING(CONVERT(varchar,c.CrimeDate,121),12,5) <= '06:00')
November 6, 2018 at 6:56 am
Jonathan AC Roberts - Tuesday, November 6, 2018 6:33 AMmarcelo salgado - Monday, November 5, 2018 6:16 PMI have this problem How can I get rows in this example.
We are looking for crimes in our database on my Crimes table, where crimes were committed from March 2018 to August 2018 from 23:00 p.m. to 06:00 a.m.
of the next day. in that table we have a datetime field where the date and time are saved.
SELECT *
FROM Crimes c
WHERE c.CrimeDate BETWEEN '20180301' AND '20180901'
AND (SUBSTRING(CONVERT(varchar,c.CrimeDate,121),12,5) >= '23:00'
OR SUBSTRING(CONVERT(varchar,c.CrimeDate,121),12,5) <= '06:00')
Jonathan....Many, many thanks !!!!
November 6, 2018 at 6:57 am
saravanatn - Monday, November 5, 2018 7:34 PMmarcelo salgado - Monday, November 5, 2018 6:16 PMI have this problem How can I get rows in this example.
We are looking for crimes in our database on my Crimes table, where crimes were committed from March 2018 to August 2018 from 23:00 p.m. to 06:00 a.m.
of the next day. in that table we have a datetime field where the date and time are saved.Kindly post DDL with sample data and expected result.
Saravanatn ...many thanks for your time....
I could solve it !!
November 6, 2018 at 8:42 am
marcelo salgado - Tuesday, November 6, 2018 6:56 AMJonathan AC Roberts - Tuesday, November 6, 2018 6:33 AMmarcelo salgado - Monday, November 5, 2018 6:16 PMI have this problem How can I get rows in this example.
We are looking for crimes in our database on my Crimes table, where crimes were committed from March 2018 to August 2018 from 23:00 p.m. to 06:00 a.m.
of the next day. in that table we have a datetime field where the date and time are saved.
SELECT *
FROM Crimes c
WHERE c.CrimeDate BETWEEN '20180301' AND '20180901'
AND (SUBSTRING(CONVERT(varchar,c.CrimeDate,121),12,5) >= '23:00'
OR SUBSTRING(CONVERT(varchar,c.CrimeDate,121),12,5) <= '06:00')Jonathan....Many, many thanks !!!!
Since you posted this in a SQL Server 2017 forum, you could also do this:
select *
from [Crimes] as [c]
where [c].[CrimeDate]
between '20180301' and '20180901'
and cast([c].[CrimeDate] as time) >= cast('23:00' as time)
or cast([c].[CrimeDate] as time) <= cast('06:00' as time);
November 6, 2018 at 9:45 am
Jonathan AC Roberts - Tuesday, November 6, 2018 6:33 AMmarcelo salgado - Monday, November 5, 2018 6:16 PMI have this problem How can I get rows in this example.
We are looking for crimes in our database on my Crimes table, where crimes were committed from March 2018 to August 2018 from 23:00 p.m. to 06:00 a.m.
of the next day. in that table we have a datetime field where the date and time are saved.
SELECT *
FROM Crimes c
WHERE c.CrimeDate BETWEEN '20180301' AND '20180901'
AND (SUBSTRING(CONVERT(varchar,c.CrimeDate,121),12,5) >= '23:00'
OR SUBSTRING(CONVERT(varchar,c.CrimeDate,121),12,5) <= '06:00')
Converting date/time data to strings is horribly expensive. You're better off using DATE/TIME functions for this.
SELECT *
FROM Crimes c
WHERE c.CrimeDate BETWEEN '20180301' AND '20180901'
AND CAST(DATEADD(HOUR, 1, c.CrimeDate) AS TIME) <= '07:00'
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 6, 2018 at 9:55 am
marcelo salgado - Tuesday, November 6, 2018 6:56 AMJonathan AC Roberts - Tuesday, November 6, 2018 6:33 AMmarcelo salgado - Monday, November 5, 2018 6:16 PMI have this problem How can I get rows in this example.
We are looking for crimes in our database on my Crimes table, where crimes were committed from March 2018 to August 2018 from 23:00 p.m. to 06:00 a.m.
of the next day. in that table we have a datetime field where the date and time are saved.
SELECT *
FROM Crimes c
WHERE c.CrimeDate BETWEEN '20180301' AND '20180901'
AND (SUBSTRING(CONVERT(varchar,c.CrimeDate,121),12,5) >= '23:00'
OR SUBSTRING(CONVERT(varchar,c.CrimeDate,121),12,5) <= '06:00')Jonathan....Many, many thanks !!!!
Since you posted this in a SQL Server 2017 forum, you could also do this:
select *
from [Crimes] as [c]
where [c].[CrimeDate]
between '20180301' and '20180901'
and cast([c].[CrimeDate] as time) >= cast('23:00' as time)
or cast([c].[CrimeDate] as time) <= cast('06:00' as time);
[/cod
marcelo salgado - Monday, November 5, 2018 6:16 PMI have this problem How can I get rows in this example.
We are looking for crimes in our database on my Crimes table, where crimes were committed from March 2018 to August 2018 from 23:00 p.m. to 06:00 a.m.
of the next day. in that table we have a datetime field where the date and time are saved.
SELECT *
FROM Crimes c
WHERE c.CrimeDate BETWEEN '20180301' AND '20180901'
AND (SUBSTRING(CONVERT(varchar,c.CrimeDate,121),12,5) >= '23:00'
OR SUBSTRING(CONVERT(varchar,c.CrimeDate,121),12,5) <= '06:00')
[/quote-4]
Jonathan....Many, many thanks !!!!
[/quote-5]
Since you posted this in a SQL Server 2017 forum, you could also do this:
select *
from [Crimes] as [c]
where [c].[CrimeDate]
between '20180301' and '20180901'
and cast([c].[CrimeDate] as time) >= cast('23:00' as time)
or cast([c].[CrimeDate] as time) <= cast('06:00' as time);
Great Lynn!!! Thanks a lot!
[/quote-6]
November 6, 2018 at 10:13 am
Lynn Pettis - Tuesday, November 6, 2018 8:42 AMmarcelo salgado - Tuesday, November 6, 2018 6:56 AMJonathan AC Roberts - Tuesday, November 6, 2018 6:33 AMmarcelo salgado - Monday, November 5, 2018 6:16 PMI have this problem How can I get rows in this example.
We are looking for crimes in our database on my Crimes table, where crimes were committed from March 2018 to August 2018 from 23:00 p.m. to 06:00 a.m.
of the next day. in that table we have a datetime field where the date and time are saved.
SELECT *
FROM Crimes c
WHERE c.CrimeDate BETWEEN '20180301' AND '20180901'
AND (SUBSTRING(CONVERT(varchar,c.CrimeDate,121),12,5) >= '23:00'
OR SUBSTRING(CONVERT(varchar,c.CrimeDate,121),12,5) <= '06:00')Jonathan....Many, many thanks !!!!
Since you posted this in a SQL Server 2017 forum, you could also do this:
select *
from [Crimes] as [c]
where [c].[CrimeDate]
between '20180301' and '20180901'
and cast([c].[CrimeDate] as time) >= cast('23:00' as time)
or cast([c].[CrimeDate] as time) <= cast('06:00' as time);
You need brackets around the two items OR'd together.
November 7, 2018 at 12:32 am
Jonathan AC Roberts - Tuesday, November 6, 2018 6:33 AMmarcelo salgado - Monday, November 5, 2018 6:16 PMI have this problem How can I get rows in this example.
We are looking for crimes in our database on my Crimes table, where crimes were committed from March 2018 to August 2018 from 23:00 p.m. to 06:00 a.m.
of the next day. in that table we have a datetime field where the date and time are saved.
SELECT *
FROM Crimes c
WHERE c.CrimeDate BETWEEN '20180301' AND '20180901'
AND (SUBSTRING(CONVERT(varchar,c.CrimeDate,121),12,5) >= '23:00'
OR SUBSTRING(CONVERT(varchar,c.CrimeDate,121),12,5) <= '06:00')
Don't use this method, performs horribly!
😎
Drew's method is the best one posted so far, shifting the time to simplify the search condition is much more efficient than dual conditions.
November 7, 2018 at 4:17 am
Eirikur Eiriksson - Wednesday, November 7, 2018 12:32 AMJonathan AC Roberts - Tuesday, November 6, 2018 6:33 AMmarcelo salgado - Monday, November 5, 2018 6:16 PMI have this problem How can I get rows in this example.
We are looking for crimes in our database on my Crimes table, where crimes were committed from March 2018 to August 2018 from 23:00 p.m. to 06:00 a.m.
of the next day. in that table we have a datetime field where the date and time are saved.
SELECT *
FROM Crimes c
WHERE c.CrimeDate BETWEEN '20180301' AND '20180901'
AND (SUBSTRING(CONVERT(varchar,c.CrimeDate,121),12,5) >= '23:00'
OR SUBSTRING(CONVERT(varchar,c.CrimeDate,121),12,5) <= '06:00')Don't use this method, performs horribly!
😎Drew's method is the best one posted so far, shifting the time to simplify the search condition is much more efficient than dual conditions.
Thank's guys for the effort to teach me
November 7, 2018 at 7:40 am
drew.allen - Tuesday, November 6, 2018 9:45 AMConverting date/time data to strings is horribly expensive. You're better off using DATE/TIME functions for this.
SELECT *
FROM Crimes c
WHERE c.CrimeDate BETWEEN '20180301' AND '20180901'
AND CAST(DATEADD(HOUR, 1, c.CrimeDate) AS TIME) <= '07:00'Drew
I'm going to put this one in my back pocket for another day. It's not intuiative but it makes so much sense. Thanks for providing that example Drew.
Cheers,
November 8, 2018 at 8:11 am
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply