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

Insert time into date/time field sql server

I have a date/time field in my sql server.  There are some records that only show the date but most show the date time and this is messing up some reports.  I want to put in a time into the records that only have the date.  Here is how I get the records with only a date: (DATEPART(hour, DateOfService) = 0)
AND (DATEPART(minute, DateOfService) = 0) AND (DATEPART(second, DateOfService) = 0)
Any idea on how I could do this.

  • 2
1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
> I want to put in a time into the records that only have the date
what time?

how does the report query?
tbailey922Author Commented:
The data is pushed into Access for reporting and when they run a report for a month it doesn't grab the the last day of the date entered if it doesn't have a time in it.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you post the relevant query part (where clause) that pushes?
I assume it uses a wrong comparison...
DBAduck - Ben MillerPrincipal ConsultantCommented:
If you have the criteria to get the records that you want to update, then you only have to decide what time you are going to put in.

If you want to put 10:00 AM into the time, then I would use the criteria you have to use an UPDATE statement.

UPDATE tblname SET datetimefield = DATEADD(hour, 10, datetimefield)
WHERE (DATEPART(hour, DateOfService) = 0)
AND (DATEPART(minute, DateOfService) = 0) AND (DATEPART(second, DateOfService) = 0)

If you want to put a different time, then you would just do more DATEADDs, or construct it using CONVERT, etc.

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

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

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