SQL Server Performance

Formatting Datetime - making time blank

Discussion in 'General DBA Questions' started by Trev256, Feb 22, 2011.

  1. Trev256 New Member

    I need to automatically insert today's date into a datetime field but ensure the time part is blank so the result is like '2011-02-23 00:00:00:000'. Does anyone know how to use the GETDATE() function to format this so the time is blank?
    Also does anyone know why the application won't edit the record because the time part is entered?
    Thanks!!!!
  2. FrankKalis Moderator

    Try DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
    Preferrably you would use the DATE data type from SQL Server 2009 onwards.
  3. mmarovic Active Member

    Hi Frank,have you updated your article about handling dateTime? It would be nice to have a link ready as an instant answer for this type of questions.
  4. FrankKalis Moderator

    I guess, it's about time to consider this seriously. It's overdue... [:$]
  5. Flexdog New Member

    datetime is just a float, thus use;
    1. cast(cast(getdate() as int) as datetime) <= fast but may not work for all versions
    or
    2. cast(floor(cast(getdate() as float)) as datetime) <= deterministic + extra 'floor' cost
  6. FrankKalis Moderator

    I believe that even with the new types available from SQL Server 2008 onwards the internal storage is still based on combining integers into a binary(...) column. As far as I'm aware it was never a float.
  7. mmarovic Active Member

    [quote user="FrankKalis"]
    I believe that even with the new types available from SQL Server 2008 onwards the internal storage is still based on combining integers into a binary(...) column. As far as I'm aware it was never a float.
    [/quote]Not sure about that. SQL Server version after 2000 borrowed a lot from ORACLE. In Oracle DATE contains time as well and it is float. BOL are not explicit about that, so I don't know, it needs to be investigated.
  8. Madhivanan Moderator

    [quote user="FrankKalis"]
    Try DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
    Preferrably you would use the DATE data type from SQL Server 2009 onwards.
    [/quote]
    [:)]
    A typo .It should be 2008 [;)]
  9. Trev256 New Member

    hi frank - this did the job! many thanks u r very helpful :) did u code this yourself or did you have this from somewhere?
  10. FrankKalis Moderator

    Thanks!
    This is a quite well-know technique for setting the time to midnight. I didn't come up with that myself, but rather picked it up from the former MS newsgroups. It is a little bit more difficult to understand why and how it works. But once this is clear, it is really an easy and elegant way. Now, I only wish we were already on SQL Server 2008 with its DATE data type, so that this workaround is not longer needed, but that's a different story. [:)]
  11. satya Moderator

    I would say Frank's contribution on DATETIME related information are quite helpful and handy to keep for every user. I always refer to his article for any date/time related development that I may get involved [:)].
  12. Madhivanan Moderator

    [quote user="Trev256"]
    I need to automatically insert today's date into a datetime field but ensure the time part is blank so the result is like '2011-02-23 00:00:00:000'. Does anyone know how to use the GETDATE() function to format this so the time is blank?
    Also does anyone know why the application won't edit the record because the time part is entered?
    Thanks!!!!
    [/quote]
    Where do you want to show formatted dates? If you use front end application, do formation there
  13. Trev256b Member

    hi frank - I worked out how the code works! Is there any place on the web where i can upload my own articles?
  14. FrankKalis Moderator

    Well, you can always go to Blogspot or Wordpress and create your own blog and start publishing. It's almost instantly and quite straightforward. really. :)
  15. Trev256b Member

    hi frank - which is the best one to use - blogspot or wordpress? i will take your advice... :)
  16. FrankKalis Moderator

    Given these two options, I would use Blogger (Blogspot) if you intend to stay on a hosted platform, or if you wfirst want to get familiar to blogging. If you later on intend to go and host somewhere your own domain, I'd go with Wordpress.
  17. satya Moderator

    Wordpress is better in this case, as you can manage the posts and directly input the blog post using an email (Windows essentials).
  18. FrankKalis Moderator

    Blogger caught up on this one recently, I believe. But, yes, really both offer all a beginner needs. No big difference at all., I mean.

Share This Page