How to add hours in SQL query

Question

How to add hours in SQL query

axaeffect 37346

Old Hand

Points: 394
More actions
December 4, 2015 at 1:32 am

#305925

Hi,
I'm querying cube my last update like this :
SELECT [CATALOG_NAME], [CUBE_NAME], [LAST_DATA_UPDATE]
FROM $System.MDSCHEMA_CUBES
I intend to add 8 hour to column LAST_DATA_UPDATE,
using DATEADD like this
SELECT [CATALOG_NAME], [CUBE_NAME], dateadd(hour,8,[LAST_DATA_UPDATE])
FROM $System.MDSCHEMA_CUBES
but it return error.
Anybody can help, how to add hours to that column ?
Thanks,
Axl

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

axaeffect 37346 (12/4/2015)
Hi,
I'm querying cube my last update like this :
SELECT [CATALOG_NAME], [CUBE_NAME], [LAST_DATA_UPDATE]
FROM $System.MDSCHEMA_CUBES
I intend to add 8 hour to column LAST_DATA_UPDATE,
using DATEADD like this
SELECT [CATALOG_NAME], [CUBE_NAME], dateadd(hour,8,[LAST_DATA_UPDATE])
FROM $System.MDSCHEMA_CUBES
but it return error.
Anybody can help, how to add hours to that column ?
Thanks,
Axl

What is the error message?

What datatype is column [LAST_DATA_UPDATE]?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

That's not T-SQL.

It is MDX? If it is, you'll need to look up the MDX date manipulation functions, they're not going to be the same as the T-SQL ones.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

<removed>... somehow duplicated my post when editing it.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

That's not T-SQL.
It is MDX? If it is, you'll need to look up the MDX date manipulation functions, they're not going to be the same as the T-SQL ones.

That's not MDX either. No ON rows, On Columns or ON 0, ON 1... After a little research...

Those are SSAS DMVs used for querying SSAS OLAP Cube metadata.

https://technet.microsoft.com/en-us/library/ms126271(v=sql.110).aspx

... and therefore would explain why the OP is getting an "error" when attempting make changes to it.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

axaeffect 37346 Old Hand Points: 394 More actions · Answer 5

Yes.. it is DMV 🙂

Can I add the column [LAST_DATA_UPDATE] ? it is for display only.

Thanks