How to embed a variable in a string?

Question

I am running the following SQL in SEDE (StackExchange Data Explorer). This SQL can specify the date and time for CreationDate, and the default value uses the value of @MonthsAgo variable.

This works fine if you enter a date and time, but you get the following error with the @MonthsAgo variable:

Conversion failed when converting date and/or time from character string.

https://data.stackexchange.com/stackoverflow/revision/1187086/1459772

declare @MonthsAgo date = cast(dateadd(month, -2, getdate()) as date)
declare @since date = ##SinceDate:string?@MonthsAgo##

select vote.PostId, post.Score, post.CreationDate from votes vote
  inner join posts post on post.Id = vote.PostId
where post.CreationDate >= @since and Tags like '%java%'
order by post.Score desc

How can I search using this variable?

Please mark the answer as an accepted if it was helpful or comment it if-else. — XAMT
– XAMT, Commented Apr 16, 2020 at 13:46

XAMT · Accepted Answer · 2020-01-26 07:41:27Z

1

The ##SinceDate:string?@MonthsAgo## is pseudocode and It isn't running in SQL Server.

StackExchange Data Explorer uses ##xxx## format to replace with Parameters that you enter on Parameter Section.

In a nutshell, If you want to convert NVarChar or Varchar to DateTime (or Date) Type :

Declare @since varchar(20)
set @since = '08-12-2012 10:15:10'
select convert(datetime, @since , 101)

Orginal Source : Here

edited Jan 26, 2020 at 7:41

answered Jan 26, 2020 at 7:18

XAMT

1,6372 gold badges15 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dmx28192 Over a year ago

Where does that conversion apply? I tried to convert the two input values to strings and convert them to DATETIME later, but that is caused same error.

XAMT Over a year ago

Parameters will replace on the server when you hit the Run Query key.

Serkan Arslan · Accepted Answer · 2020-01-27 07:12:58Z

0

You can use @MonthsAgo parameter at the query without specifying a type. If you use any type as a string, SEDE treats it like a string, not a parameter name.

declare @MonthsAgo date = cast(dateadd(month, -2, getdate()) as date)

select vote.PostId, post.Score, post.CreationDate from votes vote
  inner join posts post on post.Id = vote.PostId
  where post.CreationDate >= ##SinceDate?@MonthsAgo## and Tags like '%java%'
order by post.Score desc

answered Jan 27, 2020 at 7:12

Serkan Arslan

13.4k4 gold badges33 silver badges46 bronze badges

1 Comment

dmx28192 Over a year ago

However, that query does not return posts dated after 2019-12-31 when you specify 2019-12-31. data.stackexchange.com/stackoverflow/revision/1187659/1460544/…

Collectives™ on Stack Overflow

How to embed a variable in a string?

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related