where joinDate = dd/mm/__ - 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

SQL Server 2005 Forums

Transact-SQL (2005)

where joinDate = dd/mm/__

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-09-13 : 21:33:01

Hi,

I'm looking to do a query that brings back all members that joined on a specified date (day and month, year is irrelavant), and their total years of membership.

A result would look like this

userID / membershipYears

100 / 1
101 / 2

etc...

Anyone kind enough to help me out on this one? The "joinDate" column stores date and time info so I think that complicates things just slightly as well.

Thanks very much!
mike123


CREATE TABLE [dbo].[tblUserDetails](
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	[joinDate] [datetime] NULL)

GO

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-13 : 22:46:07

quote:
Originally posted by mike123

Hi,

I'm looking to do a query that brings back all members that joined on a specified date (day and month, year is irrelavant), and their total years of membership.

A result would look like this

userID / membershipYears

100 / 1
101 / 2

etc...

Anyone kind enough to help me out on this one? The "joinDate" column stores date and time info so I think that complicates things just slightly as well.

Thanks very much!
mike123

CREATE TABLE [dbo].[tblUserDetails](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[joinDate] [datetime] NULL)

GO

DECLARE @daymonth char(5)
SET @daymonth = '10/10'

SELECT UserId, membershipYears = DATEDIFF(year, joinDate, getdate())
FROM tblUserDetails
WHERE CONVERT ( char(5), joindate , 101 ) = @daymonth

Future guru in the making.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 02:03:06

Apply this function http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80709

E 12°55'05.25"
N 56°04'39.16"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-09-14 : 02:38:49

thanks for the suggestions! This function looks great and will come very handy, also got it working with the first suggestion

appreciated once again,
mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources