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
 How to convert Excel formula in T-Sql		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ryoka012
Starting Member

Philippines
14 Posts
Posted - 01/16/2013 :  06:36:00  Show Profile  Reply with Quote
Below is the Excel Formula that i have been working for..

=TIME(HOUR(D2),0,0)


Thanks..

James K
Flowing Fount of Yak Knowledge

1530 Posts
Posted - 01/16/2013 :  06:40:28  Show Profile  Reply with Quote
One of these perhaps? Replace GETDATE() with your column name or variable name
SELECT CAST(DATEADD(hh, DATEDIFF(hh, 0, GETDATE()), 0) AS TIME),
       DATEPART(hh, GETDATE());
Go to Top of Page

ryoka012
Starting Member

Philippines
14 Posts
Posted - 01/16/2013 :  06:57:23  Show Profile  Reply with Quote
thanks James..

Is there a way that i can pivot using the above query...
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

1530 Posts
Posted - 01/16/2013 :  08:13:47  Show Profile  Reply with Quote
Not sure what you mean ryoka. You can pivot any set of data; that is unrelated to extracting the hour part from a datetime variable/column. Can you post sample data and the results you are looking for?
Go to Top of Page

ryoka012
Starting Member

Philippines
14 Posts
Posted - 01/16/2013 :  09:33:55  Show Profile  Reply with Quote
it is hard to explain with out the data..

here is the link for the said file..

http://ul.to/43gh5m02

The pivot sheet is the processed data while the Report sheet is the raw data.

I want the data in the Pivot sheet to put in the SQL Query..

Please help..
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

1530 Posts
Posted - 01/16/2013 :  10:22:56  Show Profile  Reply with Quote
It seems to be an Excel sheet - not comfortable downloading that to my employer's computer.
Go to Top of Page

ryoka012
Starting Member

Philippines
14 Posts
Posted - 01/16/2013 :  21:46:58  Show Profile  Reply with Quote
hi,

how can i attach a file in this forum..
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7173 Posts
Posted - 01/16/2013 :  21:51:40  Show Profile  Reply with Quote
You can put the image... Do you have sample data?
Go to Top of Page

ryoka012
Starting Member

Philippines
14 Posts
Posted - 01/16/2013 :  23:16:51  Show Profile  Reply with Quote
Hi,

Image link for a formula on Day on excel..
http://ul.to/gh2bx39v

Image link for a formula on Hour on excel.
http://ul.to/f5wj17se

Link below is the pivoted data on excel..
http://ul.to/qa8s01c1
Go to Top of Page
  Previous Topic Topic Next Topic  
 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.06 seconds. Powered By: Snitz Forums 2000