Menaing of the select statement

Question

Post reply

Menaing of the select statement

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
March 29, 2017 at 7:46 am

#335937

SELECT CAST(MAX(ISNULL(AlterDate,CRDate)) as DATE
May I know what is meaning of this query

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

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

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

Can you post the whole query? Because out of context it doesn't mean all that much (and is missing a bracket)

Which of the functions don't you understand?

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

Thom A SSC Guru Points: 99127 More actions · Answer 2

Firstly, that query is incomplete, there should be a further Right Parenthesis at the end of it. (there's also no FROM clause, but I'm ignoring that).

If we break it down into parts:
ISNULL(AlterDate,CRDate)
This will return the value of AlterDate, unless it has a value of NULL. If AlterDate has the value NULL is will return the value of CRDate. If both values are NULL, then NULL will be returned.
MAX(ISNULL(AlterDate,CRDate)
This returns the maximum value of your ISNULL across the dataset, or group (if there is a group by clause).
CAST(MAX(ISNULL(AlterDate,CRDate)) as DATE)
Finally, CAST the value above to a date. Casting to a DATE would strip any time value from a datetime field. For example 2017-03-29 15:08.54.267 would become 2017-03-29.

Does that answer your question?

Edit: I "love" a good typo...

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

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

That SELECT statement is not valid, it needs one more right-parenthesis . This correct syntax for that SELECT statement:
SELECT CAST(MAX(ISNULL(AlterDate,CRDate)) as DATE)

This would be a valid query:

SELECT CAST(MAX(ISNULL(AlterDate,CRDate)) as DATE)FROM <"a table or subquery with an AlterDate and CRDate Colum">

A simple google search will help you better understand built-in SQL Server functions such as ISNULL, MAX and CAST.

"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

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 4

mcfarlandparkway

SSCertifiable

Points: 7699

March 29, 2017 at 8:13 am

#1935502

TY,Answered

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

mcfarlandparkway - Wednesday, March 29, 2017 7:46 AM
SELECT CAST(MAX(ISNULL(AlterDate,CRDate)) as DATE
May I know what is meaning of this query

mcfarlandparkway - Wednesday, March 29, 2017 8:13 AM
TY,Answered

Which part was answered?

Gail asked which function in that statement was not understood. I don't see that answered.

What was the actual answer in your opinion?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events