Hi,
I need to add 48 hours to a specific date (datetime data type) then
check if the time is past 5pm.
So for example the time is 7pm.
If this is the case I need to set the time to 5pm the next where the
final date and time will be set to a new datetime variable.
Can anyone help??
Examples would be great.
Regards
Simon 5 4136
I need to add 48 hours to a specific date (datetime data type) then
check if the time is past 5pm.
I don't fully understand your requirements and how this problem relates to
your subject but the example below should get you started.
DECLARE
@datetime1 datetime,
@datetime2 datetime
SET @datetime1= '20060802 19:00:00'
--compare only time portion
IF CAST(CONVERT(char(8), @datetime1, 114) AS datetime) '17:00:00'
BEGIN
--after 5pm: make the time 5pm and add 2 days
SET @datetime2 = CAST(CONVERT(char(8), @datetime1, 112) + ' 17:00:00' AS
datetime) + 2
END
ELSE
BEGIN
--5pm or earlier: leave time unchanged add 2 days
SET @datetime2 = @datetime1 + 2
END
SELECT @datetime1, @datetime2
--
Hope this helps.
Dan Guzman
SQL Server MVP
<si********@hotmail.comwrote in message
news:11*********************@b28g2000cwb.googlegro ups.com...
Hi,
I need to add 48 hours to a specific date (datetime data type) then
check if the time is past 5pm.
So for example the time is 7pm.
If this is the case I need to set the time to 5pm the next where the
final date and time will be set to a new datetime variable.
Can anyone help??
Examples would be great.
Regards
Simon
Here is what I think you are asking for. Start with a date, add 48
hours, and then if the result is later than 5pm, set the time to 5pm.
create table Dates (d datetime)
INSERT Dates values ('2006-08-02 08:45')
INSERT Dates values ('2006-08-02 18:45')
INSERT Dates values ('2006-08-02 22:45')
SELECT d,
CASE WHEN datepart(hour,d) >= 17
THEN dateadd(hour,17,dateadd(day,2 + datediff(day,0,d),0))
ELSE dateadd(day,2,d)
END as Future
FROM Dates
d Future
------------------------ -----------------------
2006-08-02 08:45:00.000 2006-08-04 08:45:00.000
2006-08-02 18:45:00.000 2006-08-04 17:00:00.000
2006-08-02 22:45:00.000 2006-08-04 17:00:00.000
Roy Harvey
Beacon Falls, CT
On 2 Aug 2006 03:21:35 -0700, si********@hotmail.com wrote:
>Hi,
I need to add 48 hours to a specific date (datetime data type) then check if the time is past 5pm.
So for example the time is 7pm.
If this is the case I need to set the time to 5pm the next where the final date and time will be set to a new datetime variable.
Can anyone help??
Examples would be great.
Regards Simon
On Wed, 02 Aug 2006 12:38:39 GMT, "Dan Guzman"
<gu******@nospam-online.sbcglobal.netwrote:
>I need to add 48 hours to a specific date (datetime data type) then check if the time is past 5pm.
I don't fully understand your requirements and how this problem relates to your subject but the example below should get you started.
DECLARE
@datetime1 datetime,
@datetime2 datetime
SET @datetime1= '20060802 19:00:00'
--compare only time portion IF CAST(CONVERT(char(8), @datetime1, 114) AS datetime) '17:00:00' BEGIN
--after 5pm: make the time 5pm and add 2 days
SET @datetime2 = CAST(CONVERT(char(8), @datetime1, 112) + ' 17:00:00' AS datetime) + 2 END ELSE BEGIN
--5pm or earlier: leave time unchanged add 2 days
SET @datetime2 = @datetime1 + 2 END SELECT @datetime1, @datetime2
Eww, magic numbers! And isn't Roy's solution using DatePart() and
DateAdd() a lot cleaner than converting to and from char?
Eww, magic numbers! And isn't Roy's solution using DatePart() and
DateAdd() a lot cleaner than converting to and from char?
I generally prefer CONVERT rather than DATEPART/DATEADD to isolate the time
because it makes it easier to handle more complex time expressions.
DATEPART starts to get ugly when minutes and seconds need to be considered
(e.g. '17:30:45').
Whether or not one technique is cleaner than the other is fairly subjective
but the DATEPART/DATEADD method Roy suggested probably will perform better
than CONVERT.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed Murphy" <em*******@socal.rr.comwrote in message
news:uf********************************@4ax.com...
On Wed, 02 Aug 2006 12:38:39 GMT, "Dan Guzman"
<gu******@nospam-online.sbcglobal.netwrote:
>>I need to add 48 hours to a specific date (datetime data type) then check if the time is past 5pm.
I don't fully understand your requirements and how this problem relates to your subject but the example below should get you started.
DECLARE @datetime1 datetime, @datetime2 datetime
SET @datetime1= '20060802 19:00:00'
--compare only time portion IF CAST(CONVERT(char(8), @datetime1, 114) AS datetime) '17:00:00' BEGIN --after 5pm: make the time 5pm and add 2 days SET @datetime2 = CAST(CONVERT(char(8), @datetime1, 112) + ' 17:00:00' AS datetime) + 2 END ELSE BEGIN --5pm or earlier: leave time unchanged add 2 days SET @datetime2 = @datetime1 + 2 END SELECT @datetime1, @datetime2
Eww, magic numbers! And isn't Roy's solution using DatePart() and
DateAdd() a lot cleaner than converting to and from char?
On Thu, 03 Aug 2006 01:24:48 GMT, "Dan Guzman"
<gu******@nospam-online.sbcglobal.netwrote:
>Whether or not one technique is cleaner than the other is fairly subjective but the DATEPART/DATEADD method Roy suggested probably will perform better than CONVERT.
I always used to strip off the time by converting to a character
string. I only learned the DATEADD/DATEDIFF approach recently from
reading the SQL Server newsgroups. I am told it is far more
efficient, but I am still trying to decide if it is readable. But I
have had to create some real messes with character strings and
DATEPART and such too.
I still haven't really accepted the simple date arithmatic: SET
@datetime2 = @datetime1 + 2 from your example. I know it works in
days, but I like the explicit unit DAY in DATEADD. I don't even
bother to use the abbreviations in DATEADD, DATEPART or DATENAME any
more, as I find the full word more explicit.
Roy This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Szabolcs Nagy |
last post by:
I have to measure the time of a while loop, but with time.clock i
always get 0.0s, although python manual sais:
"this is the function to use for benchmarking Python or timing
algorithms"
So i...
|
by: cournape |
last post by:
Hi there,
I have some scientific application written in python. There is a
good deal of list processing, but also some "simple" computation such
as basic linear algebra involved. I would like to...
|
by: Rebecca Smith |
last post by:
Today’s question involves two time text boxes each set to a different time
zone. Initially txtCurrentTime will be set to Pacific Time or system time.
This will change with system time as we travel...
|
by: luscus |
last post by:
Thanks for all the responses on my first question. Unfortunately the
answers I was given were too complicated for my small brain , and
neophite condition to understand. So if you could talk down to...
|
by: Edward Diener |
last post by:
In Borland's VCL it was possible to divide a component into design time and
run time DLLs. The design time DLL would only be necessary when the
programmer was setting a component's properties or...
|
by: Alfonso Morra |
last post by:
Hi I'm compiling some code and need to generate some random numbers. To
save time, I decided to use the srand, rand and time functions.
My code worked (atleast built fine) until I added time.h,...
|
by: cj |
last post by:
If I want to check to see if it's after "11:36 pm" what would I write?
I'm sure it's easy but I'm getting tired of having to work with dates
and times. Sometimes I just want time or date. And...
|
by: Luvin lunch |
last post by:
Hi,
I'm new to access and am very wary of dates as I have limited
experience in their manipulation and I know if they're not done
properly things can turn ugly quickly.
I would like to use a...
|
by: davelist |
last post by:
I'm guessing there is an easy way to do this but I keep going around
in circles in the documentation.
I have a time stamp that looks like this (corresponding to UTC time):
start_time =...
|
by: Roseanne |
last post by:
We are experiencing very slow response time in our web app. We run IIS 6 - windows 2003.
I ran iisstate.
Here's what I got. Any ideas??
Opened log file 'F:\iisstate\output\IISState-812.log'...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |