• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

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

0
David Modugno
Asked:
David Modugno
  • 4
  • 4
1 Solution
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now