SharePoint query question - 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

SQL Server 2000 Forums

Transact-SQL (2000)

SharePoint query question

Author

Topic

kdyer
Yak Posting Veteran

53 Posts

Posted - 2009-03-30 : 13:22:28

Note: the tweak with the DATEADD and -7.. When Daylight Savings ends, I will need to change it to -8. Is there a way that takes care of this automatically.


SELECT UserData.tp_ID AS Incident_ID,
 UserData.nvarchar1 AS Subject,
 UserData.ntext3 AS [Description],
 UserData.nvarchar5 AS FI_Notified,
 CASE UserData.bit1
   WHEN 0 THEN 'No'
   WHEN 1 THEN 'Yes'
 END AS Notify_Epayments,
 UserData.nvarchar4 AS Servers,
 t2.nvarchar1 AS FI_Name,
 t6.nvarchar1 AS Created_by,
 DATEADD(hh, -7,UserData.tp_Created) AS Created_Date
 FROM  UserData
 LEFT OUTER JOIN AllUserData t2
 WITH (NOLOCK, INDEX = AllUserData_PK) ON UserData.int1 = t2.tp_ID AND
 UserData.tp_RowOrdinal = 0 AND
 t2.tp_RowOrdinal = 0 AND
 t2.tp_IsCurrent = 1 AND
 t2.tp_CalculatedVersion = 0 AND
 t2.tp_DeleteTransactionId = 0 AND
 t2.tp_ListId = 'B1A2EB7D-E20E-4864-AFDC-E77B9D502986' AND
 UserData.tp_ListId = '294B664E-ADFB-413A-8ED3-B09FC78202AF'
 LEFT OUTER JOIN AllUserData t6 WITH (NOLOCK, INDEX = AllUserData_PK) ON UserData.tp_Author = t6.tp_ID AND
 UserData.tp_RowOrdinal = 0 AND
 t6.tp_RowOrdinal = 0 AND
 t6.tp_IsCurrent = 1 AND
 t6.tp_CalculatedVersion = 0 AND
 t6.tp_DeleteTransactionId = 0 AND
 t6.tp_ListId = '876B0669-9140-43A8-83CD-F7AE2BE5D006' AND
 UserData.tp_ListId = '294B664E-ADFB-413A-8ED3-B09FC78202AF'
 WHERE     (UserData.tp_ListId = '294B664E-ADFB-413A-8ED3-B09FC78202AF') AND
 (UserData.tp_IsCurrent = 1) AND
 (UserData.tp_RowOrdinal = 0) AND
 (CONVERT(CHAR,DATEADD(hh,-7,UserData.tp_Created),101) = CONVERT(CHAR,GETDATE()-1,101))
 ORDER BY UserData.tp_ID DESC

I could do this natively in SharePoint, but I am running the SQL to write out an HTML file to mail out a report that combines this information with two other systems into one file for an output.

Thanks for any insights.

Kent

souLTower
Starting Member

39 Posts

Posted - 2009-04-01 : 12:30:27

Depending on when DST starts and finishes for you.....



SELECT 
UserData.tp_ID AS Incident_ID, UserData.nvarchar1 AS Subject, UserData.ntext3 AS [Description],
UserData.nvarchar5 AS FI_Notified, 
CASE UserData.bit1 WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END AS Notify_Epayments,
UserData.nvarchar4 AS Servers, t2.nvarchar1 AS FI_Name, t6.nvarchar1 AS Created_by,

CASE WHEN datepart(month, UserData.tp_Created) > 4 and datepart(month, UserData.tp_Created) < 10
  then -- Daylight savings time
  DATEADD(hh, -8,UserData.tp_Created) 
  else -- Standard time
  DATEADD(hh, -7,UserData.tp_Created) END AS Created_Date

God Bless

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-04-01 : 12:42:55

alternatively, you could use getutcdate() function to calculate how much to subtract, something like this:

datediff(hour, getutcdate(), getdate())

UTC time is calculated based on the local time and the time zone settings on your OS.

kdyer
Yak Posting Veteran

53 Posts

Posted - 2009-04-07 : 13:19:56

quote:
Originally posted by sunitabeck

alternatively, you could use getutcdate() function to calculate how much to subtract, something like this:
datediff(hour, getutcdate(), getdate())
UTC time is calculated based on the local time and the time zone settings on your OS.

That works great! Thanks for that!

Kent

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources