Hours And Also Days - 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

Hours And Also Days

Author

Topic

mole999
Starting Member

49 Posts

Posted - 2011-01-11 : 08:10:37

Struggling to see the wood for the trees

I'm Using

GetUTCDate() -
IsNull(jbhinv_rec.work_started + +jbhinv_rec.work_started_2,
jbhinv_rec.booked_in_2 + +jbhinv_rec.booked_in) As Day,

to get a date from the server time and then use either the combined start date with start time or the booked start date with start time, all that works perfectly for me

what I can't manage to do is to take the resulting
06/05/1900 04:25:16 value and (A) present as Days, and then (B) repeating with the same query as [h]:mm

its exporting to excel and i can convert it visually no problem, but it feeds a pivot table query which when drilled down produces the 1900 date system

I've looked at CAST, and CONVERT and DATEDIFF, but frankly totally lost at this time, and no longer can construct a good keyword search

gentle pointers much appreciated

Mole

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-01-11 : 08:18:36

Maybe my brain is fried or something but I can't seem to understand your requirements.

Can you write which columns you have, which datatype they are and what you expect the output to look like...??

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

mole999
Starting Member

49 Posts

Posted - 2011-01-11 : 08:37:03

quote:
Originally posted by Lumbago

Maybe my brain is fried or something but I can't seem to understand your requirements.

Can you write which columns you have, which datatype they are and what you expect the output to look like...??

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

The relevant columns are
jbhinv_rec.work_started Varchar(5)
jbhinv_rec.work_started_2, Smalldatetime
jbhinv_rec.booked_in_2 Varchar(5)
jbhinv_rec.booked_in Smalldatetime

now if I have this right
GetUTCDate produces 11/01/2011 13:31:23
and
jbhinv_rec.booked_in_2 + +jbhinv_rec.booked_in
will produce a dd/mmm/yyyy hh:mm:ss value

I want to take the built value from the GetUTCDate which gives me a result like 06/01/1900 04:54:23

That result, I want to convert to
(A) whole days (output example 2)

and also where necessary be able to convert to

(B) total hours and minutes (output example 25:33)

before it arrives on the spreadsheet

Mole

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-11 : 10:47:50

sorry didnt understand how you got 06/01/1900 04:54:23. can you elaborate?

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

mole999
Starting Member

49 Posts

Posted - 2011-01-11 : 12:52:33

quote:
Originally posted by visakh16

sorry didnt understand how you got 06/01/1900 04:54:23. can you elaborate?

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

Can't give you an exact value as I'm now at home, but if I take my derived value from the GetUTCDate

GetUTCDate() - jbhinv_rec.work_started + +jbhinv_rec.work_started_2

i.e. 11/1/2011 09:00:00 - 10/1/2011 08:00:00 I get a value that looks like 06/05/1900 04:25:16 as the result.

If i format it in excel I get the hours or days as i need them, so I'm looking to apply the format before it gets to the spreadsheet

Mole

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-01-12 : 03:26:14

I feel kinda stupid here but I'm still not clear about the requirements. Can you provide insert statements to this table and also the *exact* format of the output you want? ->


DECLARE @jbhinv_rec table (
  work_started Varchar(5)
  work_started_2, Smalldatetime
  booked_in_2 Varchar(5)
  booked_in Smalldatetime
  )

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Sachin.Nand

2937 Posts

Posted - 2011-01-12 : 04:00:37

quote:

The relevant columns are
jbhinv_rec.work_started Varchar(5)
jbhinv_rec.work_started_2, Smalldatetime
jbhinv_rec.booked_in_2 Varchar(5)
jbhinv_rec.booked_in Smalldatetime

Why some columns have varchar datatypes to save the datetime values ?

PBUH

mole999
Starting Member

49 Posts

Posted - 2011-01-12 : 07:01:42

quote:
Originally posted by Sachin.Nand

quote:

The relevant columns are
jbhinv_rec.work_started Varchar(5)
jbhinv_rec.work_started_2, Smalldatetime
jbhinv_rec.booked_in_2 Varchar(5)
jbhinv_rec.booked_in Smalldatetime

Why some columns have varchar datatypes to save the datetime values ?

PBUH

I have no idea, thats what SQLDrill shows exists when i look at the structure. Its as the structure has been put together, and what i have to work with
Mole

mole999
Starting Member

49 Posts

Posted - 2011-01-12 : 07:11:51

quote:
Originally posted by Lumbago

I feel kinda stupid here but I'm still not clear about the requirements. Can you provide insert statements to this table and also the *exact* format of the output you want? ->

DECLARE @jbhinv_rec table (
work_started Varchar(5)
work_started_2, Smalldatetime
booked_in_2 Varchar(5)
booked_in Smalldatetime
)

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

I have READ ONLY access to the data

as for output if it can show total hours and minutes Like (67:34) also i want to be able show the 67 hours as 3 days, but obvious seperately,

some records have been open for 87 thousand hours and I need to focus in on those so we can get them closed

looking at Insert dosent seem to be what I want, I'm taking 4 columns,, if A and B are Empty then I will use C and D as in this part of the statement

IsNull(jbhinv_rec.work_started + +jbhinv_rec.work_started_2,
jbhinv_rec.booked_in_2 + +jbhinv_rec.booked_in) As Day

producing ddmmmyyyy hh:mm:ss

that is a time in the past,

so taking the current time and removing the previous from that I get the offset in hours and minutes or days since the job began,

therefore at this moment in time I know how long we have been doing the job, so even if i could store that value, it would need to be updated constantly until the work is finished

Mole

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-12 : 07:18:50

Well Mole my brain is fried too ... after looking all details ..

However I think you are finding it difficult to move the data into Excel Sheet with the format as it looks in SQL?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-01-12 : 07:27:47

I'm about to give up...is it really that hard to provide some SAMPLE data in a table format? You have read access to the database so it should be fairly simple to write some simple insert statements for some of the rows so we know what we are dealing with.

Read this article -> http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server
A good ways down it has a section called "DateTime Durations That Span Multiple Days" that will show you how do display the difference between two datetimes in days, hours, minutes and seconds. Unless you come up with some sample data this will be my last say on this matter!

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

mole999
Starting Member

49 Posts

Posted - 2011-01-12 : 07:46:15

Maybe I should state this differently

I know that

DateDiff(d, jbhinv_rec.work_started_2, GetUTCDate())

Will give me Days between the current time and the midnight of the day, but i am missing the hours, hence combining

jbhinv_rec.work_started + +jbhinv_rec.work_started_2

Can anyone tell me how to combine those two into a single date / time and name it so i can reuse that result

I still need to crack the IF one is set ISNULL then use the other, and the extra comma in DATEDIFF, is stopping that from working

I'm trying to avoid anything that is a write statement to the server as I don't have those priveldges hences doing this on the fly

Mole

mole999
Starting Member

49 Posts

Posted - 2011-01-12 : 08:03:50

quote:
Originally posted by Lumbago

I'm about to give up...is it really that hard to provide some SAMPLE data in a table format? You have read access to the database so it should be fairly simple to write some simple insert statements for some of the rows so we know what we are dealing with.

Read this article -> http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server
A good ways down it has a section called "DateTime Durations That Span Multiple Days" that will show you how do display the difference between two datetimes in days, hours, minutes and seconds. Unless you come up with some sample data this will be my last say on this matter!

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Booked_In Booked_in_2 Work_started work_Started_2
12:00 20/05/2010 08:00 20/05/2010
12:00 30/04/2010 08:00 30/04/2010
11:47 07/01/2011
09:08 18/12/2010 09:08 18/12/2010
18:00 10/01/2011 17:30 10/01/2011
07:00 11/01/2011 17:00 10/01/2011
10:00 11/12/2010
12:18 12/01/2011 12:18 12/01/2011
12:21 12/01/2011
11:05 09/07/2010 11:00 09/07/2010
12:40 10/07/2010
07:00 14/10/2010 07:00 14/10/2010
08:00 14/10/2010
19:31 21/10/2010 19:31 21/10/2010
11:30 22/10/2010 11:30 22/10/2010

some of th data has moved over, not all the booked are completed, and should workstarted

I have no idea how to put a table in here

I will go of and read that link

Mole

mole999
Starting Member

49 Posts

Posted - 2011-01-12 : 08:44:45

quote:
Originally posted by Lumbago

I'm about to give up...is it really that hard to provide some SAMPLE data in a table format? You have read access to the database so it should be fairly simple to write some simple insert statements for some of the rows so we know what we are dealing with.

Read this article -> http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server
A good ways down it has a section called "DateTime Durations That Span Multiple Days" that will show you how do display the difference between two datetimes in days, hours, minutes and seconds. Unless you come up with some sample data this will be my last say on this matter!

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

I guess I'm not comprehending INSERT. To me that would place data in the SQL datatable on the Server, thereby changing the records ?? I can't find it meaning anything else

Mole

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-12 : 09:05:16

What he wants is for you to provide insert statements so that he can run them (not you) to put data into test tables so that he can then test out queries for you.

--
Gail Shaw
SQL Server MVP

mole999
Starting Member

49 Posts

Posted - 2011-01-12 : 09:06:17

quote:
Originally posted by GilaMonster

What he wants is for you to provide insert statements so that he can run them (not you) to put data into test tables so that he can then test out queries for you.

--
Gail Shaw
SQL Server MVP

Ah that makes sense to me now, much obliged for the explanation

Mole

mole999
Starting Member

49 Posts

Posted - 2011-01-12 : 09:35:24

Is this right ?

insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
08:00,20/05/2010,12:00,20/05/2010
08:00,30/04/2010,12:00,30/04/2010
11:47,07/01/2011,,
09:08,18/12/2010,09:08,18/12/2010
17:30,10/01/2011,18:00,10/01/2011
17:00,10/01/2011,07:00,11/01/2011
10:00,11/12/2010,,
12:18,12/01/2011,12:18,12/01/2011
12:21,12/01/2011,,
11:00,09/07/2010,11:05,09/07/2010
,,12:40,10/07/2010
07:00,14/10/2010,07:00,14/10/2010
08:00,14/10/2010,,
19:31,21/10/2010,19:31,21/10/2010
11:30,22/10/2010,11:30,22/10/2010
12:00,30/12/2010,12:15,30/12/2010
10:00,30/12/2010,10:00,30/12/2010
07:00,11/01/2011,,
14:36,11/01/2011,14:36,11/01/2011
06:00,12/01/2011,06:30,12/01/2011
12:30,23/12/2010,12:40,23/12/2010
13:52,14/12/2010,13:52,14/12/2010
15:15,09/01/2011,,
20:45,11/01/2011,,
07:00,12/01/2011,,
07:00,12/01/2011,15:00,12/01/2011
14:00,31/12/2010,14:00,31/12/2010
07:51,12/01/2011,,
07:00,12/01/2011,08:00,12/01/2011
08:00,12/01/2011,09:00,12/01/2011
16:20,17/11/2010,16:20,17/11/2010
,,,
15:00,09/12/2010,,
08:45,12/01/2011,08:50,12/01/2011
08:53,12/01/2011,08:53,12/01/2011
08:30,12/01/2011,08:30,12/01/2011
10:00,12/01/2011,10:30,12/01/2011
10:30,12/01/2011,,
08:30,12/01/2011,08:30,12/01/2011
12:00,06/01/2011,,
07:00,09/12/2010,12:00,09/12/2010
08:00,11/01/2011,08:45,11/01/2011
08:30,11/01/2011,08:45,11/01/2011
13:36,12/01/2011,13:36,12/01/2011
13:44,12/01/2011,13:44,12/01/2011
12:00,12/01/2011,12:00,12/01/2011
16:45,06/01/2011,,
08:00,10/01/2011,08:00,10/01/2011
13:30,30/11/2010,13:30,30/11/2010
15:00,08/01/2011,15:00,10/01/2011
08:00,10/01/2011,,
14:00,07/01/2011,14:00,07/01/2011
08:37,06/01/2011,,
,,08:30,07/08/2010
06:30,31/08/2010,10:00,31/08/2010
14:44,02/09/2010,,
07:52,07/09/2010,07:52,07/09/2010
08:24,08/09/2010,08:24,08/09/2010
08:00,08/09/2010,08:09,08/09/2010
07:00,21/09/2010,,
06:00,22/09/2010,06:00,22/09/2010
11:27,22/09/2010,11:28,22/09/2010
10:16,24/09/2010,10:17,24/09/2010
06:00,24/09/2010,06:00,24/09/2010
08:00,10/01/2011,,
11:30,10/01/2011,,
10:00,28/09/2010,10:00,28/09/2010
10:00,07/12/2010,11:00,07/12/2010
16:00,08/01/2011,16:30,08/01/2011
07:00,05/10/2010,07:30,05/10/2010
08:30,06/10/2010,08:30,06/10/2010
01:00,02/01/2011,,
12:00,02/12/2010,12:00,08/12/2010
07:00,11/01/2011,07:00,11/01/2011
11:30,11/01/2011,11:30,11/01/2011
12:00,11/01/2011,,
19:30,11/01/2011,11:58,12/01/2011
09:00,14/12/2010,09:00,14/12/2010
09:00,11/01/2011,11:00,11/01/2011
,,10:13,09/12/2010
08:00,04/01/2011,,
11:30,30/12/2010,11:45,30/12/2010
09:00,07/01/2011,,
08:00,20/12/2010,11:30,20/12/2010
11:00,12/01/2011,11:00,12/01/2011

Mole

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-12 : 10:02:32

No. Open up Books Online (SQL help) and look at the syntax of an insert.

The first line is right, the second needs the keyword VALUES, some brackets and some quotes, and there can only be one set of values per insert statement.

--
Gail Shaw
SQL Server MVP

mole999
Starting Member

49 Posts

Posted - 2011-01-12 : 11:27:05

quote:
Originally posted by GilaMonster

No. Open up Books Online (SQL help) and look at the syntax of an insert.

The first line is right, the second needs the keyword VALUES, some brackets and some quotes, and there can only be one set of values per insert statement.

--
Gail Shaw
SQL Server MVP

OK, I will do that, sorry about learning on the fly

Mole

mole999
Starting Member

49 Posts

Posted - 2011-01-12 : 11:43:24

insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("08:00","20/05/2010","12:00","20/05/2010")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("08:00","30/04/2010","12:00","30/04/2010")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("11:47","07/01/2011","","")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("09:08","18/12/2010","09:08","18/12/2010")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("17:30","10/01/2011","18:00","10/01/2011")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("17:00","10/01/2011","07:00","11/01/2011")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("10:00","11/12/2010","","")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("12:18","12/01/2011","12:18","12/01/2011")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("12:21","12/01/2011","","")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("11:00","09/07/2010","11:05","09/07/2010")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("","","12:40","10/07/2010")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("07:00","14/10/2010","07:00","14/10/2010")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("08:00","14/10/2010","","")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("19:31","21/10/2010","19:31","21/10/2010")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("11:30","22/10/2010","11:30","22/10/2010")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("12:00","30/12/2010","12:15","30/12/2010")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("10:00","30/12/2010","10:00","30/12/2010")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("07:00","11/01/2011","","")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("14:36","11/01/2011","14:36","11/01/2011")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("06:00","12/01/2011","06:30","12/01/2011")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("12:30","23/12/2010","12:40","23/12/2010")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("13:52","14/12/2010","13:52","14/12/2010")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("15:15","09/01/2011","","")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("20:45","11/01/2011","","")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("07:00","12/01/2011","","")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("07:00","12/01/2011","15:00","12/01/2011")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("14:00","31/12/2010","14:00","31/12/2010")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("07:51","12/01/2011","","")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("07:00","12/01/2011","08:00","12/01/2011")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("08:00","12/01/2011","09:00","12/01/2011")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("16:20","17/11/2010","16:20","17/11/2010")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("","","","")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("15:00","09/12/2010","","")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("08:45","12/01/2011","08:50","12/01/2011")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("08:53","12/01/2011","08:53","12/01/2011")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("08:30","12/01/2011","08:30","12/01/2011")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("10:00","12/01/2011","10:30","12/01/2011")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("10:30","12/01/2011","","")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("08:30","12/01/2011","08:30","12/01/2011")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("12:00","06/01/2011","","")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("07:00","09/12/2010","12:00","09/12/2010")
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
VALUES ("08:00","11/01/2011","08:45","11/01/2011")

Mole

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-12 : 13:54:46

Single quotes, not double quotes, like for all strings in SQL. Plus you have ,, in the insert. Is there a column name missing or did you just put an extra ,?

Now, based on the sample data you've provided, what do you want as an output from this query?

--
Gail Shaw
SQL Server MVP

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources