Monday, April 30, 2012

Get only Date part for SQL Server DateTime value

There may be various scenarios wherein you need only Date part of an SQL Server DateTime value, rather than full DateTime value.
This is very much a requirement in case of comparing dates (only date and no datetime).

SQL Server 2008 offers a new datatype DATE which holds only Date. 
So you can simply use Convert(Date, GetDate()) to convert your full DateTime into Date. Its as simple as that. 

But if you are using a version prior to SQL Server 2008, you need to do a workaround. There are lot many ways to achieve that. Following is one of them which is being used too frequently.

Say your DateTime value is "2012-04-20 21:45:00.938"

First convert date into a varchar in format such as (dd/mm/yyyy or mm/dd/yyyy) depending upon the regional settings of the SQL Server's host environment.
SELECT CONVERT(varchar(10), GETDATE(), 101) -- 101 stands for mm/dd/yyyy (mostly in case of US)
SELECT CONVERT(varchar(10), GETDATE(), 103) -- 103 stands for dd/mm/yyyy
So, it will result into - "04/20/2012", "20/04/2012" respectively

But this is a varchar type, so you need to convert it back to DateTime type to make it able to be used in Date time operations.
Use either of this depending upon the settings in SQL server's host environment.
SELECT CONVERT(DATETIME, CONVERT(varchar(10), GETDATE(), 101) )
or
SELECT CONVERT(DATETIME, CONVERT(varchar(10), GETDATE(), 103) )

This will now result to "2012-04-20 00:00:00.000"

11 comments:

  1. It's interesting that many of the bloggers to helped clarify a few things for me as well as giving.Most of ideas can be nice content.The people to give them a good shake to get your point and across the command
    Data Science Training in Indira nagar
    Data Science Training in btm layout
    Data Science Training in Kalyan nagar
    Data Science training in Indira nagar
    Data science training in bangalore

    ReplyDelete
  2. Very awesome!!! When I searched for this I found this website at the top of all blogs in search engines.Best Digital Marketing Courses in Hyderabad

    ReplyDelete
  3. wow, great, I was wondering how to cure acne naturally. and found your site by google, learned a lot, now i’m a bit clear. I’ve bookmark your site and also add rss. keep us updated.
    data scientists training

    ReplyDelete
  4. I wanted to thank you for this great read!! I definitely enjoying every little bit of it I have you bookmarked to check out new stuff you post.
    data scientist training and placement

    ReplyDelete
  5. Thank you for the information you post and hopefully useful to readers
    Python Training in Hyderabad
    Python Course in Hyderabad

    ReplyDelete
  6. Pleasant data, important and incredible structure, as offer great stuff with smart thoughts and ideas, loads of extraordinary data and motivation, the two of which I need, because of offer such an accommodating data here. business analytics course in surat

    ReplyDelete
  7. Informative blog. Useful to many people. Thanks for sharing with us.
    Data Science Training in Hyderabad

    ReplyDelete
  8. Hi buddies, it is a great written piece entirely defined, continuing the good work constantly.
    data science online training in hyderabad

    ReplyDelete

Thanks for visiting my blog.
However, if this helped you in any way, please take a moment to write a comment.

Thanks
Nirman

Blog Archive