Time calculation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Time calculation

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2010-02-22 : 01:44:28

Hi Friends,

i have an scenario

The following are the columans

Column Datatype

FileId int
LoadStartTime time
LoadEndTime time
TimeAcknowledgementcreatd time

i have to check the TimeAcknowledgementcreatd should falls in the interval of LoadEndTime + 3hours . if it falls in the interval then no issues. if it is not then i have to return a statement that "Acknowledgement not created".

can anyone please help me write a sample query for this logic please

raky
Aged Yak Warrior

767 Posts

Posted - 2010-02-22 : 02:15:58

hi ,
try this


SELECT 
	FileID,
	LoadStartTime,
	LoadEndTime,
	TimeAcknowledgementcreatd,
	CASE WHEN DATEADD(HOUR,3,LoadEndTime) > TimeAcknowledgementcreatd THEN 'Acknowledgement not created' End 
	AS 'AcknowledgementStatus'
FROM
	[Table]

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2010-02-22 : 04:22:46

Hi raky,

there are two conditions:

1. we have to check TimeAcknowledgementcreatd should falls in the interval

2. If TimeAcknowledgementcreatd not created

how the case will be? please help me

raky
Aged Yak Warrior

767 Posts

Posted - 2010-02-22 : 05:36:15

quote:
Originally posted by sqllover

Hi raky,

there are two conditions:

1. we have to check TimeAcknowledgementcreatd should falls in the interval

2. If TimeAcknowledgementcreatd not created

how the case will be? please help me

try this

Posted - 02/22/2010 : 02:15:58 Show Profile Reply with Quote
hi ,
try this

SELECT
FileID,
LoadStartTime,
LoadEndTime,
TimeAcknowledgementcreatd,
CASE WHEN ( DATEADD(HOUR,3,LoadEndTime) > TimeAcknowledgementcreatd OR TimeAcknowledgementcreatd IS NULL ) THEN 'Acknowledgement not created' End
AS 'AcknowledgementStatus'
FROM
[Table]

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2010-02-22 : 06:23:55

thanks friend.

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2010-02-22 : 07:50:38

Hi friend, few changes in the datatype

i am using datetime as a datatype for the below columnms
LoadStartTime
LoadEndTime
TimeAcknowledgementcreatd

so how to extract the time from the date and compare the time ? please help me in this friend

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2010-02-22 : 08:04:27

Hi raky,

your logic sees to be incorrect because i told that timeacknowledgement created should falls between LoadEndTime and (LoadEndTime + 3hours) .

so how query will be please help me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-22 : 08:25:41

quote:
Originally posted by sqllover

Hi friend, few changes in the datatype

i am using datetime as a datatype for the below columnms
LoadStartTime
LoadEndTime
TimeAcknowledgementcreatd

so how to extract the time from the date and compare the time ? please help me in this friend

for extracting time see

http://visakhm.blogspot.com/2010/01/some-quick-tips-for-date-formating.html

the query should returns status as 0 or 1 and based on it show result or message at front end.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2010-02-22 : 08:43:08

hi visakh16,

From your link i got this query

SELECT DATEADD(dd,-1 * DATEDIFF(dd,0,GETDATE()),GETDATE())

but it doesn't give time part. it gives output as
1900-01-01 07:36:46.373

but i need time part only. please guide me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-22 : 08:46:12

quote:
Originally posted by sqllover

hi visakh16,

From your link i got this query

SELECT DATEADD(dd,-1 * DATEDIFF(dd,0,GETDATE()),GETDATE())

but it doesn't give time part. it gives output as
1900-01-01 07:36:46.373

but i need time part only. please guide me

it gives time part. as data type is datetime it always gives you attached default date value of 1900-01-01. but since you're looking at time diff alone i dont think this will cause you any problems.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2010-02-23 : 00:32:22

Hi visakh16

can please give me sample query to calculate the time difference using the link you have mentioned please.it would be great helpful for me.

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2010-02-23 : 07:08:01

Hi Visakh,

i done my requiremnt using the following query

SELECT loadendtime,timeackcreated,
CASE WHEN (DATEDIFF(ss,loadendtime,timeackcreated))/3600.0 > 3 THEN 'Acknowledgement not created' else 'Created' End
AS 'AcknowledgementStatus'
FROM sample_current.

It is working fine. do u have any comments on this query which i made. please let me know.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-23 : 09:21:04

quote:
Originally posted by sqllover

Hi Visakh,

i done my requiremnt using the following query

SELECT loadendtime,timeackcreated,
CASE WHEN (DATEDIFF(ss,loadendtime,timeackcreated))/3600.0 > 3 THEN 'Acknowledgement not created' else 'Created' End
AS 'AcknowledgementStatus'
FROM sample_current.

It is working fine. do u have any comments on this query which i made. please let me know.

One comment. The message to be displayed is better to be done at front end. from sql its enough just to return a bit status like 0 & 1. Then check this status and frond end and display message based on it there.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources