Using datediff - 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

Using datediff

Author

Topic

rob41
Yak Posting Veteran

67 Posts

Posted - 2010-04-01 : 20:46:16

I am trying to subtract two fields and have a third field show the percentage of the difference. Please see desired format and output below.

SELECT DATEDIFF(day,[date_received],[date_shipped]) AS [Percentage of Day]
FROM [SHIPMENTS]

date_received format is 3/3/2009 4:00 am
date_shipped format is same as date_received
percentage of day is the subtracted difference of date_received - date_shipped

all help and suggestions are greatly appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-01 : 21:45:36

I'm rather confused as to what you want, but here's a shot at it:

SELECT date_received, date_shipped, DATEDIFF(day,[date_received],[date_shipped]) AS [Percentage of Day]
FROM [SHIPMENTS]

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-01 : 22:13:56

quote:
Originally posted by rob41

I am trying to subtract two fields and have a third field show the percentage of the difference. Please see desired format and output below.

SELECT DATEDIFF(day,[date_received],[date_shipped]) AS [Percentage of Day]
FROM [SHIPMENTS]

date_received format is 3/3/2009 4:00 am
date_shipped format is same as date_received
percentage of day is the subtracted difference of date_received - date_shipped

all help and suggestions are greatly appreciated.

What you have right now is the different in days between the date_received and the date_shipped.

You wanted the "percentage of day" ? What is the definition or formula for this ?

In general when calculating percentage it is A / B * 100%. A will be the result of datediff(). What about B ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-02 : 02:13:41

quote:
Originally posted by rob41

I am trying to subtract two fields and have a third field show the percentage of the difference. Please see desired format and output below.

SELECT DATEDIFF(day,[date_received],[date_shipped]) AS [Percentage of Day]
FROM [SHIPMENTS]

date_received format is 3/3/2009 4:00 am
date_shipped format is same as date_received
percentage of day is the subtracted difference of date_received - date_shipped

all help and suggestions are greatly appreciated.

does that mean date_received is varchar type? if yes, it is recommended to use datetime fields to store date values.

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

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-02 : 03:22:07

quote:
Originally posted by khtan

quote:
Originally posted by rob41

I am trying to subtract two fields and have a third field show the percentage of the difference. Please see desired format and output below.

SELECT DATEDIFF(day,[date_received],[date_shipped]) AS [Percentage of Day]
FROM [SHIPMENTS]

date_received format is 3/3/2009 4:00 am
date_shipped format is same as date_received
percentage of day is the subtracted difference of date_received - date_shipped

all help and suggestions are greatly appreciated.

What you have right now is the different in days between the date_received and the date_shipped.

You wanted the "percentage of day" ? What is the definition or formula for this ?

In general when calculating percentage it is A / B * 100%. A will be the result of datediff(). What about B ?

KH
[spoiler]Time is always against us[/spoiler]

From the code I dont think percentage of day does have any meaning its only a term which is being used.

Vaibhav T

rob41
Yak Posting Veteran

67 Posts

Posted - 2010-04-02 : 08:14:16

I'm trying to find out the SQL code to display the difference between date_received and date_shipped and have it displayed in a percentage. Originally the code that I'm converting was in access and it was date_received - date_shipped which was equal to a percentage. I'm trying to do the same thing in SQL, hopefully I cleared it up a bit.

khtan
In (Som, Ni, Yak)

17689 Posts

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

quote:
Originally posted by rob41

I'm trying to find out the SQL code to display the difference between date_received and date_shipped and have it displayed in a percentage. Originally the code that I'm converting was in access and it was date_received - date_shipped which was equal to a percentage. I'm trying to do the same thing in SQL, hopefully I cleared it up a bit.

Go through the various reply given. If you still have problem, post some sample data and the expected result

KH
[spoiler]Time is always against us[/spoiler]

byrdzeye
Starting Member

14 Posts

Posted - 2010-04-02 : 09:11:35

If shipped in hours, then the value is 'percent of day' but technically (I think) you are just showing how long it took to ship...with real numbers.

Anyway.

SELECT convert(float,DATEDIFF(mi,[date_received],[date_shipped]))/1440 AS [Percentage of Day] FROM [SHIPMENTS]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources