|
|
Posted by Dan Guzman on 10/14/88 11:54
> 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" <emurphy42@socal.rr.com> wrote in message
news:ufj1d2l11fd7e3kib256ulghoh34huo2et@4ax.com...
> On Wed, 02 Aug 2006 12:38:39 GMT, "Dan Guzman"
> <guzmanda@nospam-online.sbcglobal.net> wrote:
>
>>> 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?
Navigation:
[Reply to this message]
|