June 29, 2015 at 9:07 am
Hi,
I have a series of time from 08:10 till 00:55 and a single date in a seperate column. What I need to have is associate the date with each time from 08:10 and when it comes after 23:55 the date needs to be incremented to the next day.
something like below
29Jun2015 08:10
29Jun2015 08:15
29Jun2015 08:20
29Jun2015 08:25
29Jun2015 08:30
29Jun2015 08:35
29Jun2015 08:40
.....
.....
....
....
29Jun2015 23:55
30Jun2015 00:00
30Jun2015 00:05
30Jun2015 00:10
30Jun2015 00:15
30Jun2015 00:20
I tried to add the based on the datepart minute however its getting added only to 00:00 and not after that.
Can anyone please help me with this....
June 29, 2015 at 9:21 am
So... You want any times that are between midnight and 8:09 to increment to the next day?
If that's correct, the following should work...
SELECT
CASE WHEN mt.MyTime >= '00:00:00' AND mt.MyTime < '08:10:00' THEN DATEADD(dd, 1, mt.MyDate) ELSE mt.MyDate END AS MyNewDate,,
mt.MyTime
FROM
#MyTable mt
HTH,
Jason
June 29, 2015 at 9:48 am
Hi,
Your solution works fine..
However I cannot have the myTime < '08:10:00' value always as '08:10:00'.
Is there any workaround for this?
June 29, 2015 at 9:51 am
karthik82.vk (6/29/2015)
Hi,Your solution works fine..
However I cannot have the myTime < '08:10:00' value always as '08:10:00'.
Is there any workaround for this?
You have several rows in your sample with a time less than '08:10:00'. Not sure what you mean by a workaround here. The solution posted seems to be working based on your very vague description. If it doesn't work then you need to explain what the problem is.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 29, 2015 at 10:09 am
Hi,
The dates and times I posted is just a sample of one record of a candidate. I will have different times for each candidate like what I posted here. The start time for the sample I posted is 08:10, however the start time can be a different for another candidate.
So I cannot have the condition mt.MyTime >= '00:00:00' AND mt.MyTime < '08:10:00' standard for all candidates.
Due to this reason only I asked like can the second condition be dynamic one without hardcoding the value as '08:10:00'
Hope this one will be clear for you.
June 29, 2015 at 10:53 am
Yea... That's simple enough we (you actually) just need to know where that information exists. Once you have that in scope, simply use that to replace the hard coded value.
June 29, 2015 at 11:45 am
Why are you storing date and time information separately? It should be stored together.
July 1, 2015 at 10:03 am
Answer has been found and hence closing the post.
July 1, 2015 at 10:17 am
Forum etiquette would have you post the answer to your question as it may help others with a similar problem.
July 1, 2015 at 11:27 am
Lynn Pettis (7/1/2015)
Forum etiquette would have you post the answer to your question as it may help others with a similar problem.
seems the answer resides here
http://www.sqlservercentral.com/Forums/Topic1699249-3077-1.aspx
________________________________________________________________
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 12:19 pm
J Livingston SQL (7/1/2015)
Lynn Pettis (7/1/2015)
Forum etiquette would have you post the answer to your question as it may help others with a similar problem.seems the answer resides here
http://www.sqlservercentral.com/Forums/Topic1699249-3077-1.aspx
True, saw that after I posted the comment here. The OP really should have posted at least a link since both threads are actually the same.
July 2, 2015 at 10:10 am
Hi,
I will close and remove this post as this has become a duplicate one.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply