May 2, 2013 at 9:42 am
I am trying to use a query that works in SQL Management in Visual Studio but the code it not translating. Can any one help me fix this. Here is the code in SQL Management.
DECLARE @StartDate DATETIME = '04/01/2013'
DECLARE @EndDate DATETIME = '04/30/2013 23:59:59'
DECLARE @UTCTimeDiff INT = -1 * (SELECT DATEDIFF(hh, GETDATE(), GETUTCDATE()))
SELECT CASE WHEN MAX(TransactionDate) IS NOT NULL
THEN CONVERT(VARCHAR, DATEADD(hh, @UTCTimeDiff, MAX(TransactionDate)))
ELSE 'No Data Present'
END AS 'Data Freshness'
FROM TotalInvoicing..TI_TransactionReporting (nolock)
I believe it has something to do with the Declare Statements but I am unsure how to remedy this error. Any help is greatly appreciated. Thank you!
-Molly
May 2, 2013 at 11:57 am
mjmshopper (5/2/2013)
I am trying to use a query that works in SQL Management in Visual Studio but the code it not translating. Can any one help me fix this. Here is the code in SQL Management.DECLARE @StartDate DATETIME = '04/01/2013'
DECLARE @EndDate DATETIME = '04/30/2013 23:59:59'
DECLARE @UTCTimeDiff INT = -1 * (SELECT DATEDIFF(hh, GETDATE(), GETUTCDATE()))
SELECT CASE WHEN MAX(TransactionDate) IS NOT NULL
THEN CONVERT(VARCHAR, DATEADD(hh, @UTCTimeDiff, MAX(TransactionDate)))
ELSE 'No Data Present'
END AS 'Data Freshness'
FROM TotalInvoicing..TI_TransactionReporting (nolock)
I believe it has something to do with the Declare Statements but I am unsure how to remedy this error. Any help is greatly appreciated. Thank you!
-Molly
You should convert this to a stored proc with parameters. Then you can execute the proc from your .net code with the parameters. What I don't understand is why you even need any parameters.
This would do the same thing.
SELECT CASE WHEN MAX(TransactionDate) IS NOT NULL
THEN CONVERT(VARCHAR, -1 * DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), MAX(TransactionDate)))
ELSE 'No Data Present'
END AS 'Data Freshness'
FROM TotalInvoicing..TI_TransactionReporting (nolock)
Also be careful with NOLOCK. Do you know all the dangers with that hint? It is far more than just potential dirty reads. You can (and will) get missing and/or duplicate data. http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 9, 2013 at 9:07 am
I tried making your changes and ended with an Error Message: Must declare teh scalar variable "@UTCTimeDIff"
May 9, 2013 at 9:11 am
mjmshopper (5/9/2013)
I tried making your changes and ended with an Error Message: Must declare teh scalar variable "@UTCTimeDIff"
You must have something else referencing that variable. It is not in the code I posted.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply