Using Dates within rows - 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 2000 Forums

SQL Server Development (2000)

Using Dates within rows

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-24 : 18:02:08

Jerry writes "This is an MS SQL Server question...but it comes from an Access Database.

I have 3 fields in a table - Month, Day, Year - all integers.

I had an Access query that could combine these three fields from every row and convert them into 1 date field...

CDate(Cal_Month & ' / ' & Cal_Day & ' / ' & Cal_Year))> Date()
which would give me something like '03/02/2000' > '03/19/2002'...to see if I would bypass that row.

I'm trying to do this now in SQL Server in a Stored Procedure.
I need to take the 3 fields and combine them into a date field that will be tested against the current date *** for each row *** in the table.

CREATE PROCEDURE [sp_Chk4SchedDates]
As
Declare @FullDate SMALLDATETIME
SELECT Calendar.Cal_Month, Calendar.Cal_Day, Calendar.Cal_Year, Clients.C_Company FROM Calendar INNER JOIN Clients
ON Calendar.Cal_Auto_Num = Clients.C_Auto_Num
WHERE (select @FullDate = Cast(Calendar.Cal_Month as varchar)+'/'+Cast(Calendar.Cal_Day as varchar)+'/'+Cast(Calendar.Cal_Year as varchar)) > GetDate()

Any ideas??

Thanx in advance.

JerryK"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-24 : 19:31:27

WHERE CONVERT(smalldatetime, Cal_Month + '/' + Cal_Day + '/' + Cal_Year,101) > GetDate()

but a couple of things-
1.why are Cal_Month, Cal_Day, Cal_Year nvarchars?
2.I've assumed you're a yank - hence the US dateformat mm/dd/yy
3.what about dates "during" that day - ie if it is 11 am when you run the code, do you want items to be excluded if the date is 9am on the same day? This code will do that.
4. I should probably ecourage you to be standard and do
WHERE CONVERT(smalldatetime, Cal_Year + Cal_Month + Cal_Day + '/' ,112) > GetDate()
which is the ISO standard way of doing it.

Hope (as they say) that helps

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-24 : 19:33:28

select *
from tbl
where dateadd(dd,tDay,dateadd(mm,tMonth,dateadd(yy,tYear-1900,'1 jan 1900'))) = convert(datetime,convert(varchar(8),getdate(),112))

or to use any indexes on the table

select *
from tbl
where tYear = datepart(yy,getdate())
and tMonth = datepart(mm,getdate())
and tDay = datepart(dd,getdate())

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Edited by - nr on 03/24/2002 19:35:09

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources