Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316

    Unanswered: Best way to get just dd/mm/yyyy part from date field?

    I would like to know the best way of returning just the Date part (dd/mm/yyyy) of a DateTime field using T-SQL. I know that some methods mean that you lose the indexing if the field is indexed. Which is the best way to do this in T-SQL whilst preserving the indexing? I'm using SQL Server 2005.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Describe what you mean by "return" in this case. The value returned to the client comes from the data, not the index, so I don't see any connection.

    Now if you are talking about using the index in the JOIN or the WHERE clause, that's a whole diffeent story. Then I usually do a fancy hampster dance using a BETWEEN clause and date expressions.

    -PatP

  3. #3
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Yes, I mean when the date forms part of the WHERE clause, eg:

    WHERE CONVERT(VARCHAR(10), CreatedDate, 103)='" & datFilterDate & "'

    Where datFilterDate is a user string that has been converted to a VB Date type.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Here's one method of truncating the time portion of a datetime value.
    No idea how it affects the index though!
    Code:
    DateAdd(dd, DateDiff(dd, 0, <yourDateTimeValue>), 0)
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    No idea how it affects the index though!
    Code:
    DateAdd(dd, DateDiff(dd, 0, <yourDateTimeValue>), 0)
    It's a function George - it shaftens it
    Heh - I think you have just wandered blithley into a mine field too
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    So any function, when used on an indexed field and used in a JOIN or WHERE clause, removes the benefits of the indexing?

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    "shaftens", "blithley"?

    You're just taking perfectly good english words and twisting them, aren't you!
    Don't go to the [strikethrough]dark[/strikethrough] red, white and blue side Poots!

    I've not done enough work on SQL recently, that part of my brain is slowly fizzling away - apologies for such a proposterous suggestion.
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why would you want to lose the benefits of indexing?
    Code:
    select ...
      from ...
     where CreatedDate >= '2008-03-27'
       and CreatedDate  < '2008-03-28'
    gives you all rows for today

    no need to strip off the time in order to compare "equals" -- just cast your net upon the waters, and return the timestamps that are greater than or equal to midnight today, as well as less than midnight tomorrow, and bob's your uncle

    note: using BETWEEN with dates and datetimes is awkward and often wrong
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2005
    Posts
    161
    If you want to preserve the use of the index, you need to perform the manipulation on your variable, not the database column. Convert &datFilterDate& is in a format SQL Server understands, such as '2008-03-27'

    Code:
    WHERE createdate = '" & datFilterDate & "'

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    cascred, that's only going to select the rows where CreatedDate is exactly midnight
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by georgev
    ... proposterous suggestion.
    Admit it georgev, you've started to wander to the dark side, too. Come on over, it's not so bad. We'll feed you some grits for breakfast and some barbequed chicken for lunch and we'll have you saying "y'all" by dinner!

    Regards,

    hmscott
    Have you hugged your backup today?

  12. #12
    Join Date
    Feb 2003
    Location
    Netherlands
    Posts
    63
    Why don't you add a computed column and add an index on that? That way you can use an index!

    Code:
    create table TMP_TEST (
      TMP_ID numeric(15) identity,
      TMP_DT datetime not null,
      TMP_DATE AS convert(varchar(10),TMP_DT,112)
    )
    go
    
    create index TMP_DATE_KEY on TMP_TEST (TMP_DATE)
    go
    
    insert into TMP_TEST (TMP_DT) values (getdate())
    go
    
    select *
    from TMP_TEST where TMP_DATE>='20080101' and TMP_DATE<='20081231'
    (I use 112 (yyyymmdd) btw so it sorts correctly, else this wouldn't come out right)
    Greetz,

    Bastiaan Olij

  13. #13
    Join Date
    Sep 2005
    Posts
    161
    Quote Originally Posted by r937
    cascred, that's only going to select the rows where CreatedDate is exactly midnight
    Of course it is. That is exactly what his original query did. I have to assume he had midnight only dates or his original query wouldn't have worked at all.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    his original query? do you mean this --

    WHERE CONVERT(VARCHAR(10), CreatedDate, 103)='" & datFilterDate & "'

    because that's is exactly what his query is not doing

    if CreatedDate were only midnight datetimes, then he wouldn't even have started this thread
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Exactly! I think I will go for the method of storing date in one field and time in another so I can preserve indexing.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •