substituate the hour part of a datetime field

Question

Post reply

substituate the hour part of a datetime field

Dominic Gagné

SSCrazy

Points: 2654
More actions
April 27, 2015 at 1:59 pm

#398383

Hi,
I have a table with a datetime field (named "lnDateDone") and an integer field (named "PlannedHour"). I want to build a new datetime field with the same value as lnDateDone and replacing the hour part by the PlannedHour field. Example:
lnDateDone = '2014-04-09 13:22:31.544'
HourPlanned = 14
new datetime field = '2014-04-09 14:00:00'
I tried the following:
CAST(SUBSTRING(CAST(lnDateDone AS VARCHAR(25)),1,12) + CAST(PlannedHour AS VARCHAR(2)) + SUBSTRING(CAST(lnDateDone AS VARCHAR(25)), 15, LEN(lnDateHeureFin)) AS SMALLDATETIME) AS DatePrevue
but it won't work when casted in DateTime format, only in varchar, but I want it in datetime as I need to do compute some values using date manipulation functions.
How can I do that??
thanks for your time and help

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101200 More actions · Answer 1

SELECT *, DATEADD(HOUR, HourPlanned, DATEADD(DAY, DATEDIFF(DAY, 0, lnDateDone), 0))

FROM (

SELECT lnDateDone = '2014-04-09 13:22:31.544', HourPlanned = 14

) AS test_data

Edit: Corrected calcs and added test data.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Dominic Gagné SSCrazy Points: 2654 More actions · Answer 2

Dominic Gagné

SSCrazy

Points: 2654

April 27, 2015 at 2:25 pm

#1793827

thanks a lot, it works!! have a nice day!!! 🙂

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 3

For fun, there are few ways of doing this:

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @dt DATETIME = '2014-04-09 13:22:31.544';

;WITH BASE_DATA(TTDATE) AS

(

SELECT DATEADD( HOUR , 14 , CONVERT( DATETIME , FLOOR( CONVERT( NUMERIC(8,1) , @dt , 0 )) , 0 ))

UNION ALL

SELECT DATEADD( HOUR , 14 , CONVERT( DATETIME , FLOOR( CONVERT( FLOAT , @dt , 0 )) , 0 ))

UNION ALL

SELECT DATEADD( HOUR , 14 , DATEADD( DAY , DATEDIFF(DAY, 0 , @dt ) , 0 ))

UNION ALL

SELECT DATEADD( HOUR , 14 , CONVERT( DATETIME , CONVERT( DATE , @dt , 0 ) , 0 ))

UNION ALL

SELECT DATEADD( HOUR , 14 , CONVERT( DATETIME , CONVERT( VARCHAR(10) , @dt , 112 ) , 112 ))

)

SELECT

BD.TTDATE

FROM BASE_DATA BD;

Results

TTDATE

-----------------------

2014-04-09 14:00:00.000

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 4

The first one has a little problem.

DECLARE @dt DATETIME = '2014-04-09 23:59:59.000';

...