SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Hours And Also Days
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

mole999
Starting Member

United Kingdom
34 Posts

Posted - 01/11/2011 :  08:10:37  Show Profile  Reply with Quote
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

Norway
3271 Posts

Posted - 01/11/2011 :  08:18:36  Show Profile  Reply with Quote
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
Go to Top of Page

mole999
Starting Member

United Kingdom
34 Posts

Posted - 01/11/2011 :  08:37:03  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/11/2011 :  10:47:50  Show Profile  Reply with Quote
sorry didnt understand how you got 06/01/1900 04:54:23. can you elaborate?

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

Go to Top of Page

mole999
Starting Member

United Kingdom
34 Posts

Posted - 01/11/2011 :  12:52:33  Show Profile  Reply with Quote
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
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 01/12/2011 :  03:26:14  Show Profile  Reply with Quote
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
Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 01/12/2011 :  04:00:37  Show Profile  Reply with Quote
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


Edited by - Sachin.Nand on 01/12/2011 04:01:12
Go to Top of Page

mole999
Starting Member

United Kingdom
34 Posts

Posted - 01/12/2011 :  07:01:42  Show Profile  Reply with Quote
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

Edited by - mole999 on 01/12/2011 07:12:45
Go to Top of Page

mole999
Starting Member

United Kingdom
34 Posts

Posted - 01/12/2011 :  07:11:51  Show Profile  Reply with Quote
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
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 01/12/2011 :  07:18:50  Show Profile  Reply with Quote
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?

Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 01/12/2011 :  07:27:47  Show Profile  Reply with Quote
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

Edited by - Lumbago on 01/12/2011 07:29:02
Go to Top of Page

mole999
Starting Member

United Kingdom
34 Posts

Posted - 01/12/2011 :  07:46:15  Show Profile  Reply with Quote
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
Go to Top of Page

mole999
Starting Member

United Kingdom
34 Posts

Posted - 01/12/2011 :  08:03:50  Show Profile  Reply with Quote
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
Go to Top of Page

mole999
Starting Member

United Kingdom
34 Posts

Posted - 01/12/2011 :  08:44:45  Show Profile  Reply with Quote
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
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/12/2011 :  09:05:16  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

mole999
Starting Member

United Kingdom
34 Posts

Posted - 01/12/2011 :  09:06:17  Show Profile  Reply with Quote
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
Go to Top of Page

mole999
Starting Member

United Kingdom
34 Posts

Posted - 01/12/2011 :  09:35:24  Show Profile  Reply with Quote
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
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/12/2011 :  10:02:32  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

mole999
Starting Member

United Kingdom
34 Posts

Posted - 01/12/2011 :  11:27:05  Show Profile  Reply with Quote
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
Go to Top of Page

mole999
Starting Member

United Kingdom
34 Posts

Posted - 01/12/2011 :  11:43:24  Show Profile  Reply with Quote
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
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/12/2011 :  13:54:46  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.3 seconds. Powered By: Snitz Forums 2000