Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
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.
Below is the Excel Formula that i have been working for..=TIME(HOUR(D2),0,0)Thanks..
James K
Master Smack Fu Yak Hacker
3873 Posts
Posted - 2013-01-16 : 06:40:28
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());
ryoka012
Starting Member
20 Posts
Posted - 2013-01-16 : 06:57:23
thanks James..Is there a way that i can pivot using the above query...
James K
Master Smack Fu Yak Hacker
3873 Posts
Posted - 2013-01-16 : 08:13:47
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?
ryoka012
Starting Member
20 Posts
Posted - 2013-01-16 : 09:33:55
it is hard to explain with out the data..here is the link for the said file..http://ul.to/43gh5m02The 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..
James K
Master Smack Fu Yak Hacker
3873 Posts
Posted - 2013-01-16 : 10:22:56
It seems to be an Excel sheet - not comfortable downloading that to my employer's computer.
ryoka012
Starting Member
20 Posts
Posted - 2013-01-16 : 21:46:58
hi,how can i attach a file in this forum..
sodeep
Master Smack Fu Yak Hacker
7174 Posts
Posted - 2013-01-16 : 21:51:40
You can put the image... Do you have sample data?
ryoka012
Starting Member
20 Posts
Posted - 2013-01-16 : 23:16:51
Hi,Image link for a formula on Day on excel..http://ul.to/gh2bx39vImage link for a formula on Hour on excel.http://ul.to/f5wj17seLink below is the pivoted data on excel..http://ul.to/qa8s01c1