July 1, 2015 at 2:52 am
I have a client data which has the candidate id, a start date which will have only the date in varchar format and a time value associated to that date in varchar format in a seperate column.
To give a brief explanation about the data a candidate will come to study center at any time point in a day for example 10:00 AM on 20-10-2014. The start date will be 20-10-2014 and from 10:00 AM he will have test based on the time point. So if the time point is 2 HR, then at 12:00 PM he will have a test. If the time point is 8 HR, it will get added with 10:00 AM and based on this he will have the test at 06:00 PM. When the timepoint reaches 00:00 the start date needs to be the next date instead of 20-10-2014.
The start date needs to be appended with the time value such that when it crosses over the time 00:00, the start date needs to get increased by 1 ie the next day. I have added the start date to the time by using the code below
CAST(STARTDATE as datetime) + CAST(CAST(STUFF([TIME],3,0,':') as time(0)) as datetime) as [EXPECTEDDATETIME]
By this above code i have created the expected datetime however
I will not be able to hardcode the value due to dynamic data. I tried to apply >= and < to the time value something like
case when MyTime >= '00:00' and MyTime < '08:10' the Dateadd(day, 1, date)
This works perfect but my concern is that I cannot put the value 08:10 because it will not a constant value for all rows.
I have provided a screenshot of my data and a expected date column for reference.
Candidate StartDateStartTimeExpected DateTime Timepoint
1 20141020 1000 2014-10-20 10:00:00 0 HR
1 201410201200 2014-10-20 12:00:00 02 HR
1 201410201400 2014-10-20 14:00:00 04 HR
1 201410201800 2014-10-20 18:00:00 08 HR
1 201410200000 2014-10-21 00:00:00 12 HR
1 201410201200 2014-10-21 12:00:00 24 HR
2 20141020 1100 2014-10-20 11:00:00 0 HR
2 201410201300 2014-10-20 13:00:00 02 HR
2 201410201500 2014-10-20 15:00:00 04 HR
2 201410201900 2014-10-20 19:00:00 08 HR
2 201410202100 2014-10-20 21:00:00 12 HR
2 201410202300 2014-10-20 23:00:00 24 HR
2 201410200230 2014-10-21 02:30:00 27 HR
2 201410201330..............
3 201410261530...............
3 201410262000
3 20141026 0930
3 20141026 1020
3 20141026 1120
I have also attached the data for reference.
Can anyone help me in this request?
July 1, 2015 at 5:26 am
July 1, 2015 at 5:30 am
why does 20141026 need to be converted 2014-10-21?
do you have any ID column that defines the order of data in your table?
please confirm that is defintely a SQL 2012 version
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 1, 2015 at 5:42 am
Hi All,
I updated the data to reflect the correct ones
July 1, 2015 at 5:43 am
Hi,
Its a type mistake... I have updated the data...
I am using sql 2012...
July 1, 2015 at 5:43 am
declare @i int,@T smallint
select @i = 20100101
Select @T=1900
select dateadd(minute,@T%100,(dateadd(hour,@T/100,
CONVERT (datetime,convert(char(8),@i))
)))
try this
Edited to say that I am now confused so I am leaving initial attempt in here, but might not work anymore.
Thought we were trying to concatenate int date and int time into a date time.
July 1, 2015 at 6:11 am
Hi,
Its not working
i tried with the date as 20101020 and time as 0030.. below is the code
declare @i int,@T smallint
select @i = 20101020
Select @T=0030
select dateadd(minute,@T%100,(dateadd(hour,@T/100,
CONVERT (datetime,convert(char(8),@i))
)))
However i am getting the result as 2010-10-20 00:30:00.000
July 1, 2015 at 8:22 am
karthik82.vk (7/1/2015)
I have a client data which has the candidate id, a start date which will have only the date in varchar format and a time value associated to that date in varchar format in a seperate column.To give a brief explanation about the data a candidate will come to study center at any time point in a day for example 10:00 AM on 20-10-2014. The start date will be 20-10-2014 and from 10:00 AM he will have test based on the time point. So if the time point is 2 HR, then at 12:00 PM he will have a test. If the time point is 8 HR, it will get added with 10:00 AM and based on this he will have the test at 06:00 PM. When the timepoint reaches 00:00 the start date needs to be the next date instead of 20-10-2014.
The start date needs to be appended with the time value such that when it crosses over the time 00:00, the start date needs to get increased by 1 ie the next day. I have added the start date to the time by using the code below
CAST(STARTDATE as datetime) + CAST(CAST(STUFF([TIME],3,0,':') as time(0)) as datetime) as [EXPECTEDDATETIME]
By this above code i have created the expected datetime however
I will not be able to hardcode the value due to dynamic data. I tried to apply >= and < to the time value something like
case when MyTime >= '00:00' and MyTime < '08:10' the Dateadd(day, 1, date)
This works perfect but my concern is that I cannot put the value 08:10 because it will not a constant value for all rows.
I have provided a screenshot of my data and a expected date column for reference.
Candidate StartDateStartTimeExpected DateTime Timepoint1 20141020 1000 2014-10-20 10:00:00 0 HR
1 201410201200 2014-10-20 12:00:00 02 HR
1 201410201400 2014-10-20 14:00:00 04 HR
1 201410201800 2014-10-20 18:00:00 08 HR
1 201410200000 2014-10-21 00:00:00 12 HR
1 201410201200 2014-10-21 12:00:00 24 HR
2 20141020 1100 2014-10-20 11:00:00 0 HR
2 201410201300 2014-10-20 13:00:00 02 HR
2 201410201500 2014-10-20 15:00:00 04 HR
2 201410201900 2014-10-20 19:00:00 08 HR
2 201410202100 2014-10-20 21:00:00 12 HR
2 201410202300 2014-10-20 23:00:00 24 HR
2 201410200230 2014-10-21 02:30:00 27 HR
2 201410201330..............
3 201410261530...............
3 201410262000
3 20141026 0930
3 20141026 1020
3 20141026 1120
I have also attached the data for reference.
Can anyone help me in this request?
The expected result cannot be achieved from the initial data, as there is no information to indicate the order in which the data has arrived. Any attempt to use windowed functions would need to know the original order of the records, and you haven't indicated that such information exists. Once data is in a SQL Server table, that original order is lost and gone forever, unless there is a column that gets populated with that information at the time the data is imported. Try and use an ORDER BY clause on this table and get the records to appear in their original order, and you'll see what I mean. Let us know if that changes...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 1, 2015 at 9:53 am
Hi All,
I found the solution...
;WITH cte
AS (
-- take the first row
SELECT Id ,
Candidate ,
MyDate ,
MyTime
FROM #TempFormatted
WHERE Id = 1
-- add next row (look at join: t.Id = cte.Id + 1)
UNION ALL
SELECT t.Id ,
t.Candidate ,
-- CASE used to compare rows and add a DAY if required
CASE WHEN t.MyTime > cte.MyTime
AND t.Candidate = cte.Candidate
THEN cte.MyDate
WHEN t.MyTime < cte.MyTime
AND t.Candidate = cte.Candidate
THEN DATEADD(DAY, 1, t.MyDate)
ELSE t.MyDate
END AS MyDate ,
t.MyTime
FROM cte
INNER JOIN #TempFormatted t ON t.Id = cte.Id + 1
)
-- output from cte
SELECT cte.Id ,
cte.Candidate ,
cte.MyDate ,
cte.MyTime ,
CONVERT(DATETIME, cte.MyDate) + CONVERT(DATETIME, cte.MyTime) FormattedValue
FROM cte
July 1, 2015 at 10:18 am
karthik82.vk (7/1/2015)
Hi All,I found the solution...
;WITH cte
AS (
-- take the first row
SELECT Id ,
Candidate ,
MyDate ,
MyTime
FROM #TempFormatted
WHERE Id = 1
-- add next row (look at join: t.Id = cte.Id + 1)
UNION ALL
SELECT t.Id ,
t.Candidate ,
-- CASE used to compare rows and add a DAY if required
CASE WHEN t.MyTime > cte.MyTime
AND t.Candidate = cte.Candidate
THEN cte.MyDate
WHEN t.MyTime < cte.MyTime
AND t.Candidate = cte.Candidate
THEN DATEADD(DAY, 1, t.MyDate)
ELSE t.MyDate
END AS MyDate ,
t.MyTime
FROM cte
INNER JOIN #TempFormatted t ON t.Id = cte.Id + 1
)
-- output from cte
SELECT cte.Id ,
cte.Candidate ,
cte.MyDate ,
cte.MyTime ,
CONVERT(DATETIME, cte.MyDate) + CONVERT(DATETIME, cte.MyTime) FormattedValue
FROM cte
please give credit where credit is due
you will note that the solution requires an ID column to order the data....which you had previously been asked about on this forum
anyways...pleased you have a solution
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 2, 2015 at 10:15 am
Hi J,
The reply I got from the Stackoverflow forum helped me to get the solution but not the answer for my scenario. In the answer I got Tanner has included a column but in my situation I cannot have a id column as i will be having incremental data which keeps on updating and changing.
So What I did is I used the ROW_NUMBER OVER ORDER BY instead of the id column and created a view that will help me to change the dates based on the time.
Then I used that view with my few other tables to get some values pivoted and got the solution.
Anyhow the reply of Tanner has helped me solved the issue.
Thanks again to Tanner.
July 2, 2015 at 11:14 am
karthik82.vk (7/2/2015)
Hi J,The reply I got from the Stackoverflow forum helped me to get the solution but not the answer for my scenario. In the answer I got Tanner has included a column but in my situation I cannot have a id column as i will be having incremental data which keeps on updating and changing.
So What I did is I used the ROW_NUMBER OVER ORDER BY instead of the id column and created a view that will help me to change the dates based on the time.
Then I used that view with my few other tables to get some values pivoted and got the solution.
Anyhow the reply of Tanner has helped me solved the issue.
Thanks again to Tanner.
kudos to you for your response......will be appreciated I am sure
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply