December 6, 2019 at 7:56 am
I have a scenario:
I have a nchar field which is tagged as start time whos value is goes like this: 2300, 2330,0900,0300,1400 so on
Other nchar field which is duration field & the values goes like 0300, 0600, 0400.
My scenario is to add the starttime field and duration field to check if the sum value should not be greater than 08:00 AM. In others words i need to exclude the rows whos value is greater than 08:00 am
Please suggest me
Regards
Janu
December 6, 2019 at 8:32 am
Can you post some table DDL (CREATE TABLE statement), sample data (INSERT statements) and expected results, please? I'm a bit confused, for example, about whether 2300 + 0900 should be treated the same as 0400 + 0400.
John
December 6, 2019 at 10:47 am
ok - nchar to store datetimes? and nchar to store durations (with leading zeros)
why not convert the fields to datetime and int - then use dateadd or datediff to fix your issue
better to fix the underlying issue rather than make your life harder when you have to pick up a support ticket in 2 years and say to yourself "oh boy this is going to be tough"
MVDBA
December 9, 2019 at 7:28 am
The starttime data is stored in 24hrs format. (0030,0130,0800, 0845, 1430,2345,2349, 2359 etc)
The duration field is nchar type > 0000, 0130,0430 etc (0430 is 04 hrs and 30 mins)
Addition of both the column has to be taken place. THe result value should not be greater than 08:00 AM
FOr Instance > Start time value is 0600 (which is 06:00 AM) and duration is 0400 which is 04 hrs) > The addition shows it is 10:00 AM. I should exclude this criteria
December 13, 2019 at 7:10 am
Any suggestion
December 13, 2019 at 7:51 am
You haven't give us the table structure as requested. Also your examples are not readily consumable sample data.
So, maybe this guess about your data will help
CREATE TABLE #SampleData (
ID int IDENTITY(1,1) NOT NULL
, StartTime nchar(4) NOT NULL
, Duration nchar(4) NOT NULL
);
INSERT INTO #SampleData ( StartTime, Duration )
VALUES ( '2300', '0300' )
, ( '2300', '0900' )
, ( '0400', '0400' )
, ( '0600', '0130' )
, ( '0600', '0230' );
DECLARE @MaxTime time(0) = '08:00:00';
SELECT sd.ID
, sd.StartTime
, sd.Duration
, FinishTime = CAST(CAST(STUFF( sd.StartTime, 3, 0, ':' ) AS datetime)
+ CAST(STUFF( sd.Duration, 3, 0, ':' ) AS datetime) AS time(0))
FROM #SampleData AS sd
WHERE @MaxTime <= CAST(CAST(STUFF( sd.StartTime, 3, 0, ':' ) AS datetime)
+ CAST(STUFF( sd.Duration, 3, 0, ':' ) AS datetime) AS time(0));
December 17, 2019 at 4:55 pm
The Above sample one works fine as it is. When I include it in my SP. It throws the below error.
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
BOth the columns are nchar> Data_Type = -8, Precision = 4, Length =8. Column_Def = ('0200')
Please suggest.
December 17, 2019 at 6:02 pm
The Above sample one works fine as it is. When I include it in my SP. It throws the below error.
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
BOth the columns are nchar> Data_Type = -8, Precision = 4, Length =8. Column_Def = ('0200')
Please suggest.
My sample was a guess at what your data looks like. There is no way that we can help you, unless you help us.
We cannot see your data, so you will need to find the record(s) that result in the error. Then we can try and identify why it is causing the error.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply