September 4, 2010 at 2:19 pm
Hi, i am having trouble trying to write a sql query in 2008 using both date and time, the date values. The date is only stored as a date i.e. 05-01-2010, my problem arises when i try to write a query that extracts data across two days but using time values. As an example i would want to extract workers who were working night shift so i would want everyone from the 23/08/2010 with a time >= 22:00:00 to 24/08/2010 with a time <= 06:00:00
This report is being run using Business Objects but i figure once i understand the SQL i shall be able to get this working, i don't wish to go down the route of creating a date time field along the lines of 23/08/2010 22:00:00 - so does anyone have any ideas as to how to get this to work?
many thanks,
Gordon
Gordon Barclay
September 4, 2010 at 2:28 pm
Your post appears to be somewhat confusing... if the date is only stored as a date say 09/04/2010 ... then how do we know if that entry was for the day shift, second shift or overnight?
If you would post your table definition(s), some sample data, and the required results, (Refer to the first link in my signature block for examples how to perform this) then someone will be able to assist you with a tested solution.
September 4, 2010 at 2:46 pm
Ron,
If the date is 23/08/2010 the subsequent time field for that row will define if it is night shift or not so a single row would have the following
Date / Time
23/08/2010 / 12:34:45
23/08/2010 / 12:45:00
24/08/2010 / 06:00:00
Therefore any records that have a date 23/08/2010 and time >= 22:00:00 and a date 24/08/2010 and a time <= 06:00:00 are night shift - sorry about any confusion.
Sorry but i cant provide any data, you could create a table and populate it - as i have done for testing;
Table;
CREATE TABLE [dbo].[tbl_DateTime](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Date] [datetime] NULL,
[Time] [time](7) NULL,
[Amount] [money] NULL,
[Year] [char](4) NULL,
[Yearchar] [char](10) NULL
SQL;
Declare @date as datetime
DECLARE @Time as Time
DECLARE @Value AS Money
DECLARE @Upper INT;
DECLARE @Lower INT;
DECLARE @YearChar Char(10)
SET @date = GETDATE ()-20
SET @Time = '00:00:00'
SET @Lower = 1
SET @Upper = 9999999
SET @YearChar = CONVERT(CHAR(10),getdate()-20,103)
WHILE @date < GETDATE()
BEGIN
SET @Value = ((@Upper - @Lower -1) * RAND() + @Lower)
INSERT INTO tbl_DateTime ([date],[Time],[Year],[Amount], [Yearchar]) VALUES(@date ,@Time,'2010', @Value, @YearChar )
SET @Time = DATEADD(MINUTE ,1,@Time)
SET @YearChar = CONVERT(CHAR(10),@date ,103)
If @Time = '23:00:00.000'
SET @date = DATEADD(Day,1,@date)
If @Time = '23:00:00.000'
SET @Time = '00:00:00'
End
The date field is there for my guidance, the yearchar field is what mimics what i am working with.
Gordon Barclay
September 4, 2010 at 5:54 pm
Is this what you're looking for?
SELECT *
FROM tbl_DateTime
WHERE CONVERT(DATETIME, YearChar, 103) + Time BETWEEN '20100823 22:00:00' AND '20100824 06:00:00'
September 5, 2010 at 12:22 am
Old Hand,
Whilst that will work, i did actually state on the initial post "i don't wish to go down the route of creating a date time field along the lines of 23/08/2010 22:00:00 - so does anyone have any ideas as to how to get this to work?"
Of course building a date and time field would work, but does anyone have any ideas without going down this route. The reason being the report is being written in WEBI (Business Objects) and i can't alter/create new objects for my client so i can only work with what i have and these are two separate Date and Time fields
Gordon Barclay
September 5, 2010 at 10:56 am
It would seem that joining the date and time is the only way;
SELECT
CONVERT(char(10), YearChar, 103) + ' '+ CONVERT(Char(8), tbl_DateTime.Time,13),
Cast(tbl_DateTime.Time as char(8))
FROM
tbl_DateTime
WHERE
CONVERT(char(10), YearChar, 103) + ' '+ CONVERT(Char(8), tbl_DateTime.Time,13) BETWEEN '01/09/2010 23:40:00' AND '02/09/2010 01:59:00'
Gordon Barclay
September 5, 2010 at 11:33 am
Gordon Barclay (9/5/2010)
It would seem that joining the date and time is the only way;SELECT
CONVERT(char(10), YearChar, 103) + ' '+ CONVERT(Char(8), tbl_DateTime.Time,13),
Cast(tbl_DateTime.Time as char(8))
FROM
tbl_DateTime
WHERE
CONVERT(char(10), YearChar, 103) + ' '+ CONVERT(Char(8), tbl_DateTime.Time,13) BETWEEN '01/09/2010 23:40:00' AND '02/09/2010 01:59:00'
That won't work as illustrated in the example below
SELECT
CONVERT(char(10), YearChar, 103) + ' '+ CONVERT(Char(8), tbl_DateTime.Time,13),
Cast(tbl_DateTime.Time as char(8))
FROM
tbl_DateTime
WHERE
CONVERT(char(10), YearChar, 103) + ' '+ CONVERT(Char(8), tbl_DateTime.Time,13) BETWEEN '23/08/2010 23:40:00' AND '02/09/2010 01:59:00'
September 5, 2010 at 12:35 pm
I'm slightly confused. The test you show has datetime values in it. Are you not using date data types and time data types.
The issue is that you have times that reset in value when we cross days. That means that essentially you have to code two queries here as I see it. You need one that grabs start times that re beyond some time, say 20:00 AND before midnight, and a second query that grabs start times from midnight to the next shift start, on each day. You can union those together.
If you have to do this for a lot of days, it could get more complicated, but I'm wondering what types of queries you are writing here. Are you counting people on a particular shift across time? If you can frame the question better, it might help us help you.
Some code I used.
CREATE TABLE DateTimeTest
( idval INT
, DateStart DATE
, TimeStart TIME
, shift CHAR(1)
)
go
INSERT datetimeTest
SELECT 1, '9/4/2010', '16:00', 'D'
union
SELECT 2, '9/4/2010', '22:00', 'D'
union
SELECT 3, '9/5/2010', '04:00', 'D'
union
SELECT 4, '9/5/2010', '06:00', 'D'
union
SELECT 5, '9/5/2010', '10:00', 'D'
-- night shift start 9/4/2010
SELECT IDval, DateStart, TimeStart
FROM dbo.DateTimeTest
WHERE ( DateStart = '9/4/2010' AND TimeStart >= '20:00' )
UNION
-- night shift start 9/5/2010
SELECT IDval, DateStart, TimeStart
FROM dbo.DateTimeTest
WHERE ( datestart = '9/5/2010' AND TimeStart < '08:00')
September 5, 2010 at 1:37 pm
Steve Jones - Editor (9/5/2010)
I'm slightly confused. The test you show has datetime values in it. Are you not using date data types and time data types.
steve-893342 (9/4/2010)
SELECT *
FROM tbl_DateTime
WHERE CONVERT(DATETIME, YearChar, 103) + Time BETWEEN '20100823 22:00:00' AND '20100824 06:00:00'
Steve
As I understand it, the actual data Gordon is working on does not have a Date or Datetime field but rather a char(10) field holding English style dates such as 25/08/2010. There is however a separate Time data field as well. I posted a solution, as above, which seems to solve the problem but I'm not sure this is what Gordon is after. I'm guessing you would need to adapt your solution to use the char(10) field rather the Date.
September 6, 2010 at 2:12 am
Steve,
"Steve
As I understand it, the actual data Gordon is working on does not have a Date or Datetime field but rather a char(10) field holding English style dates such as 25/08/2010. There is however a separate Time data field as well. I posted a solution, as above, which seems to solve the problem but I'm not sure this is what Gordon is after. I'm guessing you would need to adapt your solution to use the char(10) field rather the Date."
You are quite correct, however i don't understand the point made earlier
That won't work as illustrated in the example below
SELECT
CONVERT(char(10), YearChar, 103) + ' '+ CONVERT(Char(8), tbl_DateTime.Time,13),
Cast(tbl_DateTime.Time as char(8))
FROM
tbl_DateTime
WHERE
CONVERT(char(10), YearChar, 103) + ' '+ CONVERT(Char(8), tbl_DateTime.Time,13) BETWEEN '01/09/2010 23:52:00' AND '02/09/2010 00:10:00' -
sorry but it does work and i have tested it
If i run the above SQL i get the following results;
01/09/2010 23:52:0011:52PM
01/09/2010 23:53:0011:53PM
01/09/2010 23:54:0011:54PM
01/09/2010 23:55:0011:55PM
01/09/2010 23:56:0011:56PM
01/09/2010 23:57:0011:57PM
01/09/2010 23:58:0011:58PM
02/09/2010 00:00:0012:00AM
02/09/2010 00:01:0012:01AM
02/09/2010 00:02:0012:02AM
02/09/2010 00:03:0012:03AM
02/09/2010 00:04:0012:04AM
02/09/2010 00:05:0012:05AM
02/09/2010 00:06:0012:06AM
02/09/2010 00:07:0012:07AM
02/09/2010 00:08:0012:08AM
02/09/2010 00:09:0012:09AM
02/09/2010 00:10:0012:10AM
Unless i am missing something this is the correct result?
Anyway thanks for all the help, i did look at a union query - but as i mentioned i am using Business Objects WEBI to create the report and therefore can only use the values that are in the Universe for which the report is being run against.
P.S. It is British time not English - there is no such thing as English time rather the time in England.
Gordon Barclay
September 7, 2010 at 7:14 am
Steve Jone's code can be simplified to
SELECT IDval, DateStart, TimeStart
FROM dbo.DateTimeTest
WHERE ( (DateStart = '9/4/2010' AND TimeStart >= '20:00') or (datestart = '9/5/2010' AND TimeStart < '08:00'))
Failing to plan is Planning to fail
September 7, 2010 at 10:17 am
Will convert the nightshift duration into float and write a between clause help?
Eg. something like:
between convert(float, convert(datetime, '8/23/2010 22:00:00'))
and
convert(float, convert(datetime, '8/24/2010 6:00:00'))
September 8, 2010 at 1:57 am
Thanks for all the help, but i have done is something like
SELECT *
FROM tablea a
WHERE a.value ='xxx'
OR a.EndDate < '24/08/2010'
AND a.EndDate = '24/08/2010'
AND a.StartTime >= '22:00:00'
OR a.EndDate > '29/08/2010'
AND a.EndDate = '29/08/2010'
AND a.StartTime >= '07:00:00'
Gordon Barclay
September 8, 2010 at 2:13 am
Gordon Barclay (9/8/2010)
Thanks for all the help, but i have done is something likeSELECT *
FROM tablea a
WHERE a.value ='xxx'
OR a.EndDate < '24/08/2010'
AND a.EndDate = '24/08/2010'
AND a.StartTime >= '22:00:00'
OR a.EndDate > '29/08/2010'
AND a.EndDate = '29/08/2010'
AND a.StartTime >= '07:00:00'
You need to use braces properly otherwise you may get unexpected result
Failing to plan is Planning to fail
September 8, 2010 at 2:26 am
That is true, that is why i added the word "like" to my comment, i actually have these in the SQL - sorry if i gave the impression that this was not the case.
Failing to add pointless comments is neither here nor there...
Gordon Barclay
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply