Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: Where myDate = '2005-05-31'

    how can i search a date without the time

    in my database the column myDate = 2005-05-31 10:25:41

    how can I return all the column with :
    Where myDate = '2005-05-31'
    Where myDate <> '2005-05-31'
    Where myDate > '2005-05-31'
    Where myDate < '2005-05-31'

    not depending on the time ?

    thank you
    Last edited by anselme; 12-16-05 at 06:39.

  2. #2
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    hi

    try this

    SELECT field1,field2... fieldn from table where
    CONVERT(VARCHAR(10),datefield,110) = 'mm-dd-yyyy'
    Cheers....

    baburajv

  3. #3
    Join Date
    Dec 2005
    Posts
    266
    it works
    but is there a way to search wwith universal time yyyy-mm-dd ?
    thank you

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by anselme
    it works
    but is there a way to search wwith universal time yyyy-mm-dd ?
    thank you
    Check out CAST and CONVERT in BoL - it has every date format you could want (probably)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Dec 2005
    Posts
    266
    Check out CAST and CONVERT in BoL - it has every date format you could want (probably)

    sorry but I dont understand your awnswer

    I got it in that way
    CONVERT(VARCHAR(10),datefield,111) = 'yyyy/mm/dd'

    but if i get a value as 2005/12/4 it doesn't work, i must do vb net script to add a 0 to get 04

    is there a way to get

    2005/12/4 or 2005/12/04
    or
    2005/2/31 or 2005/02/31

    in any case ?

    thank you

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by anselme
    Check out CAST and CONVERT in BoL - it has every date format you could want (probably)
    BoL = Books Online = SQL Server Help. The "CAST and CONVERT" entry has all the arguments for date conversions.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    hi

    Quote Originally Posted by anselme
    [I]
    but if i get a value as 2005/12/4 it doesn't work, i must do vb net script to add a 0 to get 04

    is there a way to get

    2005/12/4 or 2005/12/04
    or
    2005/2/31 or 2005/02/31

    in any case ?

    thank you

    try

    convert(smalldatetime, yourDateString,111)

    but now the qn is how to eliminate the timepart (00:00:00) ?
    Cheers....

    baburajv

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use this to truncate a datetime value to only the date portion:
    select dateadd(day, datediff(day, 0, [YourDateTime]), 0)
    This method uses only mathematical operations, and is more efficient than using CAST or CONVERT, which require string operations.

    Either way, if you are running this function on your data then you will lose the benefit of any indexing on you datetime column, which is going to impart performance.

    Depending on indexing, you may get better performance filtering with something like this:
    where [YourDateTime] >= [FilterDate] and [YourDateTime] < dateadd(day, 1, [FilterDate])
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Dec 2002
    Posts
    1,245
    Wouldn't he get better performance by using BETWEEN?

    ie:
    Code:
    DECLARE @MyDateParam varchar(10)
    
    SELECT @MyDateParam = '12/01/2005'
    
    SELECT
        <mycols>
    FROM
        <MyTable>
    WHERE
        MyDateCol BETWEEN Cast(@MyDateParam as datetime) AND Cast(@MyDateParam + ' 23:59:59.999' as datetime)
    Have you hugged your backup today?

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm not sure about the relative performance of BETWEEN. It's performing the same logical operation, but I suppose it's possible that it is optimized in some way the comparison operators are not.
    Just gotta remember that BETWEEN is inclusive, so make sure that your second parameter is one time-tick short of midnight.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The SQL BETWEEEN operator can ride an index if one exists. Because of the use of a function, the Convert() must do a table scan. If the index exists (which it should), the between ought to be much faster.

    -PatP

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    We were discussing the performance differences between

    WHERE DATEVALUE BETWEEN DATEA AND DATEB
    -vs.-
    WHERE DATEVALUE >= DATEA AND DATEVALUE < DATEB
    ...which should also take advantage of indexes.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by blindman
    We were discussing the performance differences between

    WHERE DATEVALUE BETWEEN DATEA AND DATEB
    -vs.-
    WHERE DATEVALUE >= DATEA AND DATEVALUE < DATEB
    ...which should also take advantage of indexes.
    blindman,

    Sorry, actually I missed your post entirely. I was suggesting an alternate to using the Convert function and didn't see that you had already put them onto a better track. Your solution is (as usual) more elegant.

    Regards,

    hmscott
    Have you hugged your backup today?

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oops, my bad. Sorry. BETWEEN is actually implemented within the engine as two conditional tests, so there is no practical difference.

    -PatP

  15. #15
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    Quote Originally Posted by Pat Phelan
    Oops, my bad. Sorry. BETWEEN is actually implemented within the engine as two conditional tests, so there is no practical difference.

    -PatP
    pat,

    does that mean

    select col1 from table where col2 BETWEEN 1 AND 100
    and
    select col1 from table where col2 >=1 and col2 <=100
    are one and the same ?
    Cheers....

    baburajv

Posting Permissions

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