SELECT statement help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SELECT statement help

Author

Topic

colgie
Starting Member

18 Posts

Posted - 2010-09-03 : 05:01:01

I have a table with 4 fields called expirytable:

Name Expiryday ExpiryMonth Expiryyear
The fields contain the format:
Name: Whatever
Expiryday: 20
Expirymonth: AUG
Expriryyear: 2010

I need to SELECT Name FROM expirytable
WHERE whole expiry date ie: AUG 20 2010
is greater than or equal to the date two days ago.

Can someone help please?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-03 : 05:26:49

[code]DECLARE @Sample TABLE
(
Name VARCHAR(20) NOT NULL,
ExpiryDay TINYINT NOT NULL,
ExpiryMonth CHAR(3) NOT NULL,
ExpiryYear SMALLINT NOT NULL
)

INSERT @Sample
SELECT 'Whatever', 20, 'AUG', 2010 UNION ALL
SELECT 'Peso', 1, 'SEP', 2010

-- Solution here
SELECT Name,
ExpiryDay,
ExpiryMonth,
ExpiryYear
FROM @Sample
WHERE CONVERT(DATETIME, CAST(ExpiryDay AS VARCHAR(4)) + ' ' + ExpiryMonth + ' ' + CAST(ExpiryYear AS VARCHAR(4)), 106) >= DATEADD(DAY, DATEDIFF(DAY, 2, GETDATE()), 0)[/code]

N 56°04'39.26"
E 12°55'05.63"

colgie
Starting Member

18 Posts

Posted - 2010-09-03 : 06:07:35

Thanks a million - looks like just what I need.
I'll work on it and post more thanks when I get it to work.
Thanks again

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-03 : 07:42:49

You can also make a calculated column like this

ALTER TABLE	ExpiryTable
ADD		ExpiryDate AS (CONVERT(DATETIME, CAST(ExpiryDay AS VARCHAR(4)) + ' ' + ExpiryMonth + ' ' + CAST(ExpiryYear AS VARCHAR(4)), 106))

And then your code is much easier to read

-- Solution here
SELECT	Name,
	ExpiryDay,
	ExpiryMonth,
	ExpiryYear
FROM	ExpiryTable
WHERE	ExpiryDate >= DATEADD(DAY, DATEDIFF(DAY, 2, GETDATE()), 0)

N 56°04'39.26"
E 12°55'05.63"

colgie
Starting Member

18 Posts

Posted - 2010-09-03 : 08:25:41

Does the calculated column calculate as new entries are entered or do you have to do the recalculation every time you access?
By the way - the code you already gave me is working - thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources