Madhivanan's TSQL Blog

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Blogs

Upload Image

Close it

Select File


	Like

Invite Friends

Browse by Tags · View All

sql_server	217
t-sql	211
tsql	116
sqlserver	96
BRH	78
#SQLServer	66
#TSQL	56
SQL Server	34
function	11
SSMS	9

Archive · View All

August 2007	17
August 2010	8
June 2012	7
June 2011	7
November 2007	7
August 2012	6
May 2012	6
November 2011	6
August 2011	6
October 2011	6

Home · Posts · Most Liked · Most Discussed · Ask a Question · Fans (385) · Preferences

Have you ever used getdate(something)?

Aug 8 2011 4:52AM by Madhivanan

One of my friends asked me to ask him a very different and tricky interview question. I asked him to come up with the code that passes parameter to getdate() function ie use any code where getdate(something) is part of the code. Although it is very interesting he did not come up with an answer. Here is the answer for this trickey query.

It is not possible to pass a parameter to getdate function. The follows fails

select getdate(5)

with the following error

Msg 174, Level 15, State 1, Line 2
The getdate function requires 0 argument(s).

So the correct way is to make getdate as table name and use column alias along with it

Run this and see the output

select * from
(
select 1 as number
) getdate(a)

Another method is to make use of CTE

with getdate(col) as 
(
select 4
) 
select col from getdate

Have you ever thought of this getdate this way?

Tags: tsql, SQL Server, #TSQL, #SQLServer,

Previous: Answer to a question and win a book at sqlauthority.com
Next: Question of the month August 2011 - select 100.-89_90

Madhivanan

3 · 39% · 12419

Liked

Lifesaver

Refreshed

Learned

Incorrect


Like this
Post this to my facebook wall
Tweet this
This was a true life saver
Thanks, this helped me to refresh my memory
Very informative, I just learned something new
I disagree, this information is incorrect


	Submit

10 Comments

Good to know. I learn something new today...

commented on Aug 9 2011 1:23AM

Hardik Doshi

20 · 9% · 2839

Thanks Hardik for the feedback

commented on Aug 9 2011 4:38AM

Madhivanan

3 · 39% · 12419

And a real world application of this would be?

commented on Aug 9 2011 1:47PM

Cris

191 · 1% · 242

Cris, As I said in the blog post, there is no real world case in which it is neccessary. It is only for a trickey interview question

commented on Aug 10 2011 1:46AM

Madhivanan

3 · 39% · 12419

Madhivanan, Sorry, I missed that.

But in reality the code above still does not call the getdate() function and pass it a parameter. The string of characters as used in the code above is simply a text string used as a table alias.

commented on Aug 10 2011 8:41AM

Cris

191 · 1% · 242

Cris, that is my point. It is not about passing parameter to getdate function. In the example getdate is treated as table alias and the parameter is treated as a column alias. It is just a trick that we can use getdate that way

commented on Aug 10 2011 8:49AM

Madhivanan

3 · 39% · 12419

What exactly is the point of this?

Does this relate to GETDATE in any way? As a "tricky interview" question, this is completely pointless. It serves no use in the real world and has nothing to do with the GETDATE function.

SELECT * FROM (SELECT 1 as number) GETDATE(A) returns a single row, with a column "A" with a value of 1. SELECT * FROM (SELECT 1 as number) FOOBAR(A) returns a single row, with a column "A" with a value of 1.

The only thing that I've learned from this is that you can add column aliases to a table alias. I'd still prefer the "traditional" way to assigning table aliases as it would be supportable.

SELECT foobar.number AS A FROM (SELECT 1 as number) foobar

commented on Aug 13 2011 12:11PM

Marc Jellinek

97 · 2% · 545

How about this?

create function dbo.getdate(@id int) returns varchar(20) begin

declare @out varchar(20)

select @out = date from (select 1 as id, convert(char(10), getdate(), 102) as date union all select 2, convert(char(10), getdate(), 104) ) as x where id = @id

return @out

end

And then select dbo.getdate(1) returns 2011.08.17

and select dbo.getdate(2) returns 17.08.2011

This is it... passing parameter to getdate function, and it has to do with the getdate(). Nothing special, but maybe better answer for this question.

commented on Aug 17 2011 10:57AM

Slobodan Bogunovic

71 · 2% · 777

That's good example Slobodan Bogunovic. You can also use a procedure with the name getdate and pass a parameter to it

commented on Aug 18 2011 4:33AM

Madhivanan

3 · 39% · 12419

Slobodan, that is an excellent example.

commented on Aug 19 2011 8:41AM

Cris

191 · 1% · 242

Your Comment

Sign Up or Login to post a comment.

"Have you ever used getdate(something)?" rated 5 out of 5 by 5 readers +Madhivanan