# SQL statement question - defining a variable
**URL:** https://boards.straightdope.com/t/sql-statement-question-defining-a-variable/556403
**Category:** Factual Questions
**Created:** [October 7, 2010, 8:01pm UTC](https://boards.straightdope.com/t/sql-statement-question-defining-a-variable/556403 "2010-10-07T20:01:12Z")
**Posts on this page:** 10
**Page:** 1
### Author:  [@Kinthalis](https://boards.straightdope.com/u/Kinthalis)
#### Post date: [October 7, 2010, 8:01pm UTC](https://boards.straightdope.com/t/sql-statement-question-defining-a-variable/556403/1 "2010-10-07T20:01:12Z")
I’m kind of stuck. I’m more an LINQ guy, haven’t touched much SQL in a while. I’ve got the query below where I’m retrieving records that fall within 9:00 am to 9:00pm in a particular timezone. As you can see the TimeZone entry in the DB is a string and I’m using a CASE statement to convert it to an int.
Is there some way that I can do that once and set the result to a variable I can re-use for all subsequent calls? I don’t like having to repeat it all the time.
```auto
SELECT ID, TimeZone
FROM dbo.Table1
WHERE CONVERT(CHAR(8),DATEADD(hour,
CASE dbo.Table1.TimeZone
WHEN 'East' THEN 0
WHEN 'Central' THEN -1
WHEN 'Mountain' THEN -2
WHEN 'West' THEN -3
END, CURRENT_TIMESTAMP),8) > '09:00:00'
AND CONVERT(CHAR(8),DATEADD(hour,
CASE dbo.Table1.TimeZone
WHEN 'East' THEN 0
WHEN 'Central' THEN -1
WHEN 'Mountain' THEN -2
WHEN 'West' THEN -3
END, CURRENT_TIMESTAMP),8) < '21:00:00'
AND DATEADD(hour,
CASE dbo.Table1.TimeZone
WHEN 'East' THEN 0
WHEN 'Central' THEN -1
WHEN 'Mountain' THEN -2
WHEN 'West' THEN -3
END, CURRENT_TIMESTAMP) > appointment
```
---
### Author:  [@Shagnasty](https://boards.straightdope.com/u/Shagnasty)
#### Post date: [October 7, 2010, 8:15pm UTC](https://boards.straightdope.com/t/sql-statement-question-defining-a-variable/556403/2 "2010-10-07T20:15:19Z")
In straight SQL, nope. You have to repeat it which does get repetitive but it works. There are types of SQL like Oracle PL/SQL that allow variables but straight SQL won’t do it.
---
### Author:  [@Discipline](https://boards.straightdope.com/u/Discipline)
#### Post date: [October 7, 2010, 8:22pm UTC](https://boards.straightdope.com/t/sql-statement-question-defining-a-variable/556403/3 "2010-10-07T20:22:22Z")
Make a timezone conversion table variable and join to it
so:
```auto
DECLARE @TimeZoneMath TABLE (
TimeZone varchar(10) null,
HourOffset int null
)
INSERT @TimeZoneMath (TimeZone, HourOffset) values ('East', 0)
SELECT ID, TimeZone
FROM dbo.Table1 as t
JOIN @TimeZoneMath as m on t.TimeZone = m.TimeZone
WHERE t.hour + m.dateoffset > appointment.
```
Or something like that anyway.
---
### Author:  [@friedo](https://boards.straightdope.com/u/friedo)
#### Post date: [October 7, 2010, 8:23pm UTC](https://boards.straightdope.com/t/sql-statement-question-defining-a-variable/556403/4 "2010-10-07T20:23:39Z")
**Shagnasty** is right. Pure SQL is mostly declarative and doesn’t allow variables. A better way would be to define a view with the timezone already converted to an int, then run queries against that. A stored procedure (in whatever procedure language your DB uses) would also work.
---
### Author:  [@Punoqllads](https://boards.straightdope.com/u/Punoqllads)
#### Post date: [October 7, 2010, 8:27pm UTC](https://boards.straightdope.com/t/sql-statement-question-defining-a-variable/556403/5 "2010-10-07T20:27:01Z")
If your SQL system supports subqueries, you could do:
```auto
SELECT ID, TimeZone
FROM
(
SELECT ID,
TimeZone,
DATEADD(hour,
CASE TimeZone
WHEN 'East' THEN 0
WHEN 'Central' THEN -1
WHEN 'Mountain' THEN -2
WHEN 'West' THEN -3
END, CURRENT_TIMESTAMP) AS adjusted_hour,
CONVERT(CHAR(8), adjusted_hour, 8) AS adjusted_hour_string
FROM db0.Table1
WHERE adjusted_hour > appointment
) AS subq
WHERE adjusted_hour_string >= '09:00:00' AND adjusted_hour_string < '21:00:00'
```
---
### Author:  [@crazyjoe](https://boards.straightdope.com/u/crazyjoe)
#### Post date: [October 7, 2010, 8:51pm UTC](https://boards.straightdope.com/t/sql-statement-question-defining-a-variable/556403/6 "2010-10-07T20:51:43Z")
> [@Discipline](#):
>
> Make a timezone conversion table variable and join to it
>
> so:
>
> ```auto
>
> DECLARE @TimeZoneMath TABLE (
> TimeZone varchar(10) null,
> HourOffset int null
> )
>
> INSERT @TimeZoneMath (TimeZone, HourOffset) values ('East', 0)
>
> SELECT ID, TimeZone
> FROM dbo.Table1 as t
> JOIN @TimeZoneMath as m on t.TimeZone = m.TimeZone
> WHERE t.hour + m.dateoffset > appointment.
>
> ```
>
> Or something like that anyway.
This is the winning answer, or the start of it anyhow, if you are using Microsoft SQL Server 2000 or greater.
---
### Author:  [@Kinthalis](https://boards.straightdope.com/u/Kinthalis)
#### Post date: [October 7, 2010, 9:00pm UTC](https://boards.straightdope.com/t/sql-statement-question-defining-a-variable/556403/7 "2010-10-07T21:00:19Z")
Thanks guys! I’ll give the JOIN a shot.
---
### Author:  [@Imasquare](https://boards.straightdope.com/u/Imasquare)
#### Post date: [October 8, 2010, 9:50am UTC](https://boards.straightdope.com/t/sql-statement-question-defining-a-variable/556403/8 "2010-10-08T09:50:39Z")
What DB Server are you using? If it’s SQL Server then you can create a user defined function that will return either 0, -1, -2, -3 depending on what string value is passed into it.
Then you can your re-write SQL to incorporate the function like this…
```auto
SELECT ID, TimeZone
FROM dbo.Table1
WHERE CONVERT(CHAR(8),DATEADD(hour, dbo.GetTimeZone(dboTable1.TimeZone), CURRENT_TIMESTAMP) > appointment
```
This may be possible with other Database servers as well, but SQL Server is the only one I am familiar with.
---
### Author:  [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)
#### Post date: [October 9, 2010, 2:28pm UTC](https://boards.straightdope.com/t/sql-statement-question-defining-a-variable/556403/9 "2010-10-09T14:28:24Z")
Microsoft SQL Server starting with the 2005 version has something called “common table expressions” which are another way to accomplish the same thing as joining a temp table or whipping up a UDF.
In terms of functionality CTEs are about like a subquery or table-valued UDF. The one awesome thing CTEs offer which the others don’t is an elegant way to express recursive queries. This feature isn’t relavant to the OP’s immediate need, but is still good info to know for next time.
if I was the OP and had SQL Server 2005 or later, CTEs would be my first choice.
---
### Author:  [@crazyjoe](https://boards.straightdope.com/u/crazyjoe)
#### Post date: [October 11, 2010, 3:03am UTC](https://boards.straightdope.com/t/sql-statement-question-defining-a-variable/556403/10 "2010-10-11T03:03:14Z")
> [@Imasquare](#):
>
> What DB Server are you using? If it’s SQL Server then you can create a user defined function that will return either 0, -1, -2, -3 depending on what string value is passed into it.
>
> Then you can your re-write SQL to incorporate the function like this…
>
> ```auto
>
> SELECT ID, TimeZone
> FROM dbo.Table1
> WHERE CONVERT(CHAR(8),DATEADD(hour, dbo.GetTimeZone(dboTable1.TimeZone), CURRENT_TIMESTAMP) > appointment
>
> ```
>
> This may be possible with other Database servers as well, but SQL Server is the only one I am familiar with.
While creating a function is convenient, it hasto execute the function code for each row you need to calculate. By creating a table an joining to it, you cut down massively on CPU requirements.