2

Our database with a data type of datetime has some entries that are just dates and some with dates and times. For the entries with just dates, can we run a script that will set the time portion to midnight (0.00:00) but leave the date intact?

UPDATE: There seems to be some confusion with the question so I'll try to elaborate a little. When we query the database to get the order of events, they returned results are not ordered as expected since some results have dates only and others have dates and times. For the purpose of this query, we could careless about the time of the entry, just the date which is why we thought about just updating the existing dates to midnight.

HPWD
  • 2,232
  • 4
  • 31
  • 61
  • sure why not? Is your question how to do this? See: http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server – John Sobolewski Jun 07 '13 at 15:53
  • @jsobo post this link as an answer and I'll accept it. – HPWD Jun 07 '13 at 15:59
  • I think you mean to say "for the entries with datetimes"? If so, why not just change the datatype to Date? It doesn't sound like you are using the time anyway. – wilsjd Jun 07 '13 at 16:35
  • @wilsjd We thought about it but we aren't sure if that would truncate the existing dates. We also still need to find in the application where the time string is coming from, too. But that is outside the scope of this issue at hand. :D – HPWD Jun 07 '13 at 16:36
  • Good to be thinking in those terms but it won't, you'd be fine - altering a column to be DATE from DATETIME will not lose the date, it will just truncate the time. -http://stackoverflow.com/questions/12432515/t-sql-changing-datetime-to-date-data-type – wilsjd Jun 07 '13 at 16:38
  • 2
    @dlackey just convert the column to Date and you will be fine. – Zane Jun 07 '13 at 16:43
  • 1
    I think your question is essentially a duplicate of the one @jsobo has posted the link to. (I mean, it basically boils down to that question.) Voting to close this as a duplicate. – Andriy M Jun 09 '13 at 12:00

1 Answers1

2

I seriously don't understand this question. So, the column is of datatype DATETIME, right?. So, if you add a value that is just date, then it will have the 00:00:00.00 as the time part, are you saying that it's not currently like that?, because then I suspect that you don't actually have a DATETIME column.

Lamak
  • 69,480
  • 12
  • 108
  • 116
  • We inherited the database and it is old so we suspect the datatype was changed. That is the only logical explanation we can come up with for the missing time stamp. – HPWD Jun 07 '13 at 16:00
  • @Leigh That would also work. – HPWD Jun 07 '13 at 16:01
  • 1
    @dlackey you *suspect* the datatype was changed?, can't you verify that? – Lamak Jun 07 '13 at 16:02
  • @dlackey And, are you sure that you are talking about the column datatype and not about a varchar column that has dates on it? – Lamak Jun 07 '13 at 16:07
  • @Leigh No, the group who maintained the database has been "dismissed." We are flying blind and trying to figure out why they did what they did. You gotta love reverse engineering. – HPWD Jun 07 '13 at 16:29
  • jsobo posted a link to another SO question which will satisfy our needs (querying purposes). Just waiting for him to post it as the answer so I can mark it correct. http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server – HPWD Jun 07 '13 at 16:30
  • @dlackey Ok, no problem. But as I see it, that post is a different question – Lamak Jun 07 '13 at 16:31
  • it is but we were too close to the problem needed to step back and see it through different eyes. The SO link provided was like a "duh" moment for us. :D – HPWD Jun 07 '13 at 16:35