Help With DateTime Values
-
I'm trying to figure out the simplist way to determine that 2:00 AM Is Between
8:00 PM and 3:59 AM.
For Example, I want a stored proc or a function that would return row 3 if I passed in 2:00 AM:
1 4:00:00 AM 11:59:59 AM
2 12:00:00 PM 7:59:59 PM
3 8:00:00 PM 3:59:59 AM
Thanks,
Pete
-
I would try something like:
I used @time for an example based on getdate(), yous would be much simpler to use.
Select row_id from table
where (select cast(datepart(hh,@time)as varchar)+':'+cast(datepart(mi,@time)as varchar)) <TIME_COL1 and (select cast(datepart(hh,@time)as varchar)+':'+cast(datepart(mi,@time)as varchar))>TIME_COL2
-
How is the time stored? If it's as varchar forget it. VARCHAR is a string value and strings are ordered (sorted) alphanumerically.
1
10
11
2
20
25
3
This is why dates and times should always be stored as DATETIME data types, even if it means using a default date or time.
-SQLBill
-
create function dbo.DateRow(@DATEIN DateTime)
returns int
as
begin
declare @RETVAL int
declare @THEDAY DateTime
-- truncate supplied date for the day in question
set @THEDAY=cast(convert(varchar(11),@DATEIN,113) as DateTime)
set @RETVAL=
case when
@DATEIN < dateadd(Hour,4,@THEDAY) or
@DATEIN >= dateadd(Hour,20,@THEDAY) then 3
else case when
@DATEIN < dateadd(Hour,12,@THEDAY) then 1
else 2
end end
return(@RETVAL)
end
-
Here's an approach that will handle the shifts more generically -- for instance, if the shift hours change you'll change the shift table, not the SQL code.
Logical Design:
1. Think of the problem based on a 24 hour clock:
1 04:00:00 11:59:59
2 12:00:00 19:59:59
3 20:00:00 03:59:59
2.To express all of your shifts with start time less than end time, break
shift 3 into 2 parts:
1 04:00:00 11:59:59
2 12:00:00 19:59:59
3 20:00:00 23:59:59
3 00:00:00 03:59:59
Physical Design:
3. Define the shift times as DATETIME. Here's a table that meets those requirements:
CREATE TABLE [dbo].[aa_shifts] (
[shift_iid] [int] IDENTITY (1, 1) NOT NULL ,
[shift_cd] [char] (1) NULL ,
[start_tm] [datetime] NULL ,
[end_tm] [datetime] NULL
) ON [PRIMARY]
GO
Implementation:
4. Fill it:
INSERT INTO dbo.aa_shifts (shift_cd,start_tm,end_tm) VALUES('1','04:00:00','11:59:59')
INSERT INTO dbo.aa_shifts (shift_cd,start_tm,end_tm) VALUES('2','12:00:00','19:59:59')
INSERT INTO dbo.aa_shifts (shift_cd,start_tm,end_tm) VALUES('3','20:00:00','23:59:59')
INSERT INTO dbo.aa_shifts (shift_cd,start_tm,end_tm) VALUES('3','00:00:00','03:59:59')
5. Display it: SELECT shift_cd,start_tm,end_tm FROM dbo.aa_shifts
1 1900-01-01 04:00:00.000 1900-01-01 11:59:59.000
2 1900-01-01 12:00:00.000 1900-01-01 19:59:59.000
3 1900-01-01 20:00:00.000 1900-01-01 23:59:59.000
3 1900-01-01 00:00:00.000 1900-01-01 03:59:59.000
Important note: SQL Server puts these "pure times" with a date of January 1, 1900.
This is an important implementation detail.
6. Handle the target time as a DATETIME. In the worst case, it is a current date,
and you'll have to convert it to a time on Jan 1, 1900.
For example:
DECLARE @now DATETIME, @basedate DATETIME, @time DATETIME
SELECT @now = '11/30/2004 2 AM'
PRINT @now
-------------------
Nov 30 2004 2:00AM
7. Convert your current time to a pure time like this:
SELECT @basedate = MIN(start_tm) FROM dbo.aa_shifts
SELECT @time = dateadd(d,-datediff(d,@basedate,@now),@now)
PRINT @time
----------------
Jan 1 1900 2:00AM
(*)Thanks to postings from Frank Kalis for insight into date and time arithmetic.
8. Now you can select the correct shift code:
SELECT shift_cd FROM dbo.aa_shifts
WHERE start_tm <= @time
AND end_tm >= @time
-----------------------
3
9. Here's the SQL; you can put it in a stored proc
DECLARE @now DATETIME, @basedate DATETIME, @time DATETIME
SELECT @now = '11/30/2004 2 AM' --getdate()
SELECT @basedate = MIN(start_tm) FROM dbo.aa_shifts
SELECT @time = dateadd(d,-datediff(d,@basedate,@now),@now)
SELECT shift_cd
FROM dbo.aa_shifts
WHERE start_tm <= @time
AND end_tm >= @time
A long answer, but it was fun.
Regards,Bob Monahon
-
One more way...
--DROP TABLE dates
GO
CREATE TABLE dates (id int PRIMARY KEY, date1 datetime, date2 datetime)
DECLARE @date datetime
SET @date = '02:00 AM'
SET NOCOUNT ON
INSERT dates VALUES (1, '4:00:00', '11:59:59')
INSERT dates VALUES (2, '12:00:00', '19:59:59')
INSERT dates VALUES (3, '20:00:00', '03:59:59')
INSERT dates VALUES (4, '01:00:00', '03:59:59')
SET NOCOUNT OFF
SELECT id,
CONVERT(varchar(12), date1, 108) AS StartTime,
CONVERT(varchar(12), @date, 108) AS TimeToCheck,
CONVERT(varchar(12), date2, 108) AS EndTime
FROM dates
WHERE ( CONVERT(varchar(12), @date, 108) BETWEEN CONVERT(varchar(12), date1, 108) AND CONVERT(varchar(12), date2, 108) )
OR ( CONVERT(varchar(12), date1, 108) >= CONVERT(varchar(12), @date, 108)
AND CONVERT(varchar(12), @date, 108) < CONVERT(varchar(12), date2, 108)
AND CONVERT(varchar(12), date1, 108) >= CONVERT(varchar(12), date2, 108)
)
-
hmmmm. Complicated replies, indeed.
The answer, as always, depends: This data, how it is stored? Properly or not?
More information, you must provide.
-
Thank you all for your replies.
Thank you Bob for your very detailed response. It sounds like you've encountered this issue before.
Great solutions!
-
hmmm.
Define a standard "base date", you might, then store times only you can. Hiding the date, a formatting issue it becomes, hmm?
The following constraint, consider it you will:
check (check (datediff(dd,0,TransTime) = 0)
Using standard SQL Server "base date" of 1-1-1900, converted from the numeric value 0, Yoda is.
Confuse not storage with presentation, even you, mighty Joe Celko. Strong with the force you are, but beware you will of tempations from the dark side!
-
More help needed.
I again wanted to thank you all.
Yoda, its scary but I almost understood what you were talking about.
Bob, magnificent job in following the clues.
You were able to deduce, correctly that this was a shift table and also, based on the Subject "Help with DateTime Values" that the times were in DateTime format. Your solution works in returning the correct shift based on Time only, regardless of the date.
My new question is this:
My shift table is actually a dimension in a data warehouse. Will having 2 records for the one shift with 2 seperate ID's affect my Analyisis Services CUBES?
What is the best way to Create a shift dimension based off of your design?
Thanks again, you people are great!
-
Re: Will having 2 records for the one shift with 2 seperate ID's affect my Analyisis Services CUBES?
Answer: Probably yes.
Re: What is the best way to Create a shift dimension based off of your design?
Answer: Probably you'll need a view that collapses shift 3 back into one record. Like this:
SELECT shift_cd,
MAX(start_tm) AS shift_start_tm,
MIN(end_tm) AS shift_end_tm
FROM aa_shifts
GROUP BY shift_cd
-------------------------------------
1 1900-01-01 04:00:00.000 1900-01-01 11:59:59.000
2 1900-01-01 12:00:00.000 1900-01-01 19:59:59.000
3 1900-01-01 20:00:00.000 1900-01-01 03:59:59.000
Regards,Bob Monahon
-
Thanks again, Bob. You are awesome!
-
Joe, you wrote:
"[...] there is no such thing as a TIME datatype"
Indeed, there is no such datatype in Microsoft SQL Server 2000. But this datatype IS defined by the SQL-92 standard (among the other time-related data types: DATE, TIMESTAMP, INTERVAL, etc). I am surprised to hear from YOU that. Can you tell us if in later versions of the SQL standard this datatype was removed ?
Razvan
-
You're welcome, Peter. Glad to help.
Regards,Bob Monahon
-
>Will having 2 records for the one shift with 2 seperate ID's affect my Analyisis Services CUBES?
Here's a bit of code where you will not have two records... most of the code is test and demonstration setup...
--===== Supress auto-display of rowcounts
SET NOCOUNT ON
--===== Declare the variable for and time to find the ID for
DECLARE @TimeToFind DATETIME
SET @TimeToFind = '2:00:00 AM'
--===== If temporary test table exists, drop it
IF OBJECT_ID('TempDB..#TimeBlocks') IS NOT NULL
DROP TABLE #TimeBlocks
--===== Create the temporary test table
CREATE TABLE #TimeBlocks
(
ID INT,
StartTime DATETIME,
EndTime DATETIME
)
--===== Populate the temporary test table with the start and endtimes for the blocks
INSERT INTO #TimeBlocks
SELECT 1, '4:00:00 AM' ,'11:59:59 AM' UNION ALL
SELECT 2,'12:00:00 PM', '7:59:59 PM' UNION ALL
SELECT 3, '8:00:00 PM' , '3:59:59 AM'
--===== Add 1 day to any EndTime that is less than the StartTime
-- This is because those end times occur the next day in relation to the
-- start time
UPDATE #TimeBlocks
SET EndTime = EndTime + 1
WHERE EndTime < StartTime
--===== Find the ID of the time block that 2:00:00 AM fits between
SELECT ID
FROM #TimeBlocks
WHERE CASE
WHEN @TimeToFind < (SELECT MIN(StartTime) FROM #TimeBlocks)
THEN @TimeToFind + 1
ELSE @TimeToFIND
END
BETWEEN StartTime AND EndTime
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply