Problem using DateDiff to Calc period

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

Problem using DateDiff to Calc period

Author

Topic

Lester Vincent
Starting Member

22 Posts

Posted - 2006-01-24 : 19:33:17

Hi people. I hope you can help with this. It has me completely stumped.
I am trying to get SQLS 2000 to calculate days between dates, without any success at all.
The two dates are as imported from a text file, and are as follows: -
(@issueddate=) 3/01/2006 3:37:00 PM
(@completiondate=) 04-FEB-2006 18:00

I cannot do much about the date formats, as they are as received from another party, as an email attachment.

I have tried just about everything I know to get this working, even tried commenting out each line till practically nothing is left, and still get the same message. Have also deleted the procedure and recreating it.
I have tried removing "PM" from the first date; tried changing varchar to datetime; tried using 'dd' instead of 'd' in the DateDiff function
Because of the "Aborted" response in Query Analyser, nothing is returned, and yet the Query Analyser's "Execute"
Command reports it completed successfully.
Should I ignore this "Aborted" response altogether and assume that it works?
Is it a known bug in SQLS 2000?

Here is the Stored Procedure:
----------------------------
CREATE procedure proc_calcDeadline
(@issueddate varchar(30)='',@completiondate varchar(30)='',@return int=NULL output)
as
declare @Period int
--set @issueddate='3/01/2006 3:37:00 AM'
--set @completiondate='04-FEB-2006 18:00'
set @period=datediff(d,@issueddate,@completiondate)
--print 'period=='+cast(@period as varchar)
set @return=@period
return @return
GO

The DEBUG response in Query Analyser is as follows: -

"Aborted"

[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
When I make changes in Query Analyser, (ie, "Alter Procedure', etc" then select "EXECUTE" option
the response is: -

"The command(s) completed successfully."

PS I am STILL unable to get fully into Query Analyser for step-by-step processing, even though I created the database on a single workstation (and therefore am the dbowner, with full Admin rights).

(Homesick for VBA territory, where I can get date calcs like this done easily!!)

Lester Vincent
Sydney

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-24 : 20:05:14

I just copy and paste your code in my QA and it runs sucessfully.

CREATE procedure proc_calcDeadline
(
	@issueddate 	varchar(30)	= '',
	@completiondate varchar(30)	= '',
	@return 	int		= NULL output
)
as
declare @period int
set @period=datediff(d,@issueddate,@completiondate)
set @return=@period
return @return

go
-- execute it
declare
	@rtn	int
exec proc_calcDeadline '3/01/2006 3:37:00 PM', '04-FEB-2006 18:00', @rtn OUTPUT
select	@rtn

Result
------
-25

----------------------------------
'KH'
I do work from home but I don't do homework

Lester Vincent
Starting Member

22 Posts

Posted - 2006-01-24 : 22:54:34

Thank you, Khtan for giving this a try. Looks as though I should ignore the QA message, but wish I knew why it happens.

I still have to resolve the irritation that (as in VBA) the code always reads dates as month/day/year, instead of dmy. As the data is received in dmy format, I will have to rearrange the date string to mdy before getting the calcs done.
The correct answer(in view of this) was getting the difference between 3rd January, 2006 (ie, in dmy format, '3/1/2006') and 4th February, 2006), which is 34 days.

I will have to work out some code to do the re-formatting. If you have any SQLS suggestions, I'd appreciate that too.

Kind regards (Thanks for the quick reply!)

Lester Vincent
Sydney

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-24 : 23:03:04

If your source data is in DMY, then use convert with 103.
Try this code.

declare
	@strdate	varchar(30)

select	@strdate	= '02/03/2006'

select	convert(datetime, @strdate, 101) as USA,     -- treat strdate as MDY
	convert(datetime, @strdate, 103) as British  -- treat strdate as DMY

Result :
USA                     British                                                
----------------------- ----------------------- 
2006-02-03 00:00:00.000 2006-03-02 00:00:00.000

----------------------------------
'KH'
I do work from home but I don't do homework

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-25 : 00:09:11

Also refer
http://www.sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

Lester Vincent
Starting Member

22 Posts

Posted - 2006-01-25 : 01:06:04

Thank you both for your great help! I'm on the way with it now.

Regards,

Lester Vincent
Sydney

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources