sql 2005 update record where datetime field is today's date

I have a table that has a datetime field called TimeOut.  I need to update a record where RadioEntity = 'something' and Status = 'Out' and TimeOut = Today's date

I am not sure how write compare for just today's date... here is what i have so far.. thanks in advance for the help
UPDATE [tblCheckIn-Out]
SET Status = 'In', TimeIn = '2015-04-07 11:07:35.000'
where RadioEntity = '62 - Kincaid, Mark' and Status = 'Out' and TimeOut = 

Open in new window

David ModugnoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Assuming TimeOut is date and not datetime, give this a whirl...

Declare @dt date = CAST(GETDATE() as date) 

UPDATE [tblCheckIn-Out]
SET Status = 'In', TimeIn = '2015-04-07 11:07:35.000'
where RadioEntity = '62 - Kincaid, Mark' and Status = 'Out' and TimeOut = @dt

Open in new window

0
David ModugnoAuthor Commented:
it is stored in the db as a DateTime... but I only need to compare the date - I would think i need to convert the DateTime, but not sure how that would be done.. thanks for the help
0
David ModugnoAuthor Commented:
I should have looked closer at you answer ... i am trying it now
thanks
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>it is stored in the db as a DateTime
Ok, TimeOut is a datetime, so we need to CAST it as a date so it can compare date to date
Declare @dt date = CAST(GETDATE() as date) 

UPDATE [tblCheckIn-Out]
SET Status = 'In', TimeIn = '2015-04-07 11:07:35.000'
where RadioEntity = '62 - Kincaid, Mark' and Status = 'Out' and CAST(TimeOut as date) = @dt

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David ModugnoAuthor Commented:
this is sql 2005.. so i am getting some errors...
I made these changes.... errors are gone but it does not update the one record that meets the where clause

Declare @dt datetime 
set @dt = CAST(GETDATE() as datetime)

UPDATE [tblCheckIn-Out]
SET Status = 'In', TimeIn = '2015-04-07 11:07:35.000'
where RadioEntity = '62 - Kincaid, Mark' and Status = 'Out' and CAST(TimeOut as dateTime) = @dt

Open in new window

0
David ModugnoAuthor Commented:
Thanks for you help.. i you got me going in the right direction.. here is what I came up with - seems to be working

Declare @dt datetime
set @dt = (Select convert(varchar, getdate(),105))

UPDATE [tblCheckIn-Out]
SET Status = 'In', TimeIn = GETDATE()
where RadioEntity = '62 - Kincaid, Mark' and Status = 'Out' and (Select convert(varchar, TimeOut,105)) = @dt
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Oh.  The date data type was introduced in SQL 2008, so it can't be used in 2005.   This link looks like a handy way to convert dates in SQL 2005.

See if this is possible, as I don't have a SQL 2005 box handy..
Declare @dt char(10) 
SELECT @dt = CONVERT(char,GETDATE(),101)

UPDATE [tblCheckIn-Out]
SET Status = 'In', TimeIn = '2015-04-07 11:07:35.000'
where RadioEntity = '62 - Kincaid, Mark' and Status = 'Out' and CONVERT(char, TimeOut, 101) = @dt

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim

p.s. This would be an excellent excuse to poke your employer/client about upgrading.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.