March 4, 2017 at 1:31 am
Hi All,
Need query help. Below is my test table and test data.
create table test
(c1 int not null identity(1,1),
c2 varchar(600)
)
insert into test(c2)
SELECT 'Friday 10:00 AM - 12:00 PM PST'
UNION ALL
SELECT 'Friday 12:00 AM - 2:00 AM PST'
UNION ALL
SELECT 'Friday 12:00 PM - 2:00 PM PST'
UNION ALL
SELECT 'Friday 2:00 AM - 4:00 AM PST'
UNION ALL
SELECT 'Friday 2:00 PM - 4:00 PM PST'
UNION ALL
SELECT 'Friday 4:00 AM - 6:00 AM PST'
UNION ALL
SELECT 'Friday 4:00 PM - 6:00 PM PST'
UNION ALL
SELECT 'Friday 6:00 AM - 8:00 AM PST'
UNION ALL
SELECT 'Friday 6:00 PM - 8:00 PM PST'
UNION ALL
SELECT 'Friday 8:00 AM - 10:00 AM PST'
UNION ALL
SELECT 'Friday 8:00 PM - 10:00 PM PST'
select * from test
Expected output should be as below.
Now, I want to extract only the starting time values in the output. How can we do that?
/*
10:00
12:00
12:00
2:00
2:00
4:00
4:00
6:00
6:00
8:00
8:00
*/
Thanks,
Sam
March 4, 2017 at 2:27 am
There are many ways of doing this, here are two examples, one that extracts the character value and the other that returns a time(0) value.
My question is why would 08:00 PM be equal to 08:00 AM?
😎
USE TEEST;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.test') IS NOT NULL DROP TABLE dbo.test;
create table dbo.test
(
c1 int not null identity(1,1),
c2 varchar(600)
);
insert into dbo.test(c2)
SELECT 'Friday 10:00 AM - 12:00 PM PST' UNION ALL
SELECT 'Friday 12:00 AM - 2:00 AM PST' UNION ALL
SELECT 'Friday 12:00 PM - 2:00 PM PST' UNION ALL
SELECT 'Friday 2:00 AM - 4:00 AM PST' UNION ALL
SELECT 'Friday 2:00 PM - 4:00 PM PST' UNION ALL
SELECT 'Friday 4:00 AM - 6:00 AM PST' UNION ALL
SELECT 'Friday 4:00 PM - 6:00 PM PST' UNION ALL
SELECT 'Friday 6:00 AM - 8:00 AM PST' UNION ALL
SELECT 'Friday 6:00 PM - 8:00 PM PST' UNION ALL
SELECT 'Friday 8:00 AM - 10:00 AM PST' UNION ALL
SELECT 'Friday 8:00 PM - 10:00 PM PST';
SELECT
T.c1
,T.c2
,CONVERT(TIME(0),SUBSTRING(T.c2,CHARINDEX(' ',T.c2,1),CHARINDEX(' -',T.c2,1)- CHARINDEX(' ',T.c2,1)),0) AS TIME_TYPE
,LEFT(STUFF(T.c2,1,CHARINDEX(' ',T.c2,1),''),CHARINDEX(' ',STUFF(T.c2,1,CHARINDEX(' ',T.c2,1),''))) AS TIME_STRING
FROM dbo.test T;
Output
c1 c2 TIME_TYPE TIME_STRING
----------- ---------------------------------- ---------------- ------------
1 Friday 10:00 AM - 12:00 PM PST 10:00:00 10:00
2 Friday 12:00 AM - 2:00 AM PST 00:00:00 12:00
3 Friday 12:00 PM - 2:00 PM PST 12:00:00 12:00
4 Friday 2:00 AM - 4:00 AM PST 02:00:00 2:00
5 Friday 2:00 PM - 4:00 PM PST 14:00:00 2:00
6 Friday 4:00 AM - 6:00 AM PST 04:00:00 4:00
7 Friday 4:00 PM - 6:00 PM PST 16:00:00 4:00
8 Friday 6:00 AM - 8:00 AM PST 06:00:00 6:00
9 Friday 6:00 PM - 8:00 PM PST 18:00:00 6:00
10 Friday 8:00 AM - 10:00 AM PST 08:00:00 8:00
11 Friday 8:00 PM - 10:00 PM PST 20:00:00 8:00
March 4, 2017 at 9:52 am
Thanks a ton Erik.
March 5, 2017 at 2:10 am
vsamantha35 - Saturday, March 4, 2017 9:52 AMThanks a ton Erik.
You are very welcome.
😎
March 5, 2017 at 12:08 pm
vsamantha35 - Saturday, March 4, 2017 9:52 AMThanks a ton Erik.
The questions remains, though... why are you treating 8:00AM and 8:00PM as if they were the same? Sounds like really bad mojo and monkey will die. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply