Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2009
    Posts
    1

    Unanswered: Select a date between dates in two fields in a table

    Hi All,

    Table1
    =================
    BGN_DT End_DT
    ----------------------- -----------------------
    2009-06-27 00:00:00.000 2009-08-07 00:00:00.000


    I am trying to retrieve the above row in table1 with a input of BGN_DT >= '2009-07-09' AND BGN_DT <= '2009-07-20'

    I am want the above row to written because my input value BGN_DT= 2009-07-09 is within the range of the table1.bgn_dt and table1.end_dt

    Can anyone please tell What would be the SQL statement to do that?

    Thanks for the help in advance

    Regards,
    Kama

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Have you looked at the BETWEEN operator in Books Online?
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Mar 2009
    Posts
    47
    Hi u can try this Select * from table1 where convert(datetime,BGN_DT,105) between convert(datetime,'2009-07-09' ,105) and convert(datetime,'2009-07-20',105)

  4. #4
    Join Date
    Apr 2007
    Posts
    183
    Where End_dt >= '2009-07-09' And Bgn_dt <= '2009-07-20'
    Microsoft SQL Server MVP

    N 5604'39.26"
    E 1255'05.63"

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by SnehaAgrawal
    Hi u can try this Select * from table1 where convert(datetime,BGN_DT,105) between convert(datetime,'2009-07-09' ,105) and convert(datetime,'2009-07-20',105)
    Why convert? You've just made any indexes unusable.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    ...also, I'm always interested when non-Americans use that date format Peter. I thought I would check something - do use US or Swedish language settings?
    Code:
    DECLARE   @i    AS TINYINT
            , @l    AS NVARCHAR(20)
            , @dc1  AS CHAR(12)
            , @dc2  AS CHAR(10)
            , @d    AS DATETIME
    
    SELECT    @dc1  = '2009-12-30'
            , @dc2  = REPLACE(@dc1, '-', '')
            , @i    = 0
    
    WHILE @i <= 32 
    BEGIN
        
        SELECT    @l    = name
                , @i    = @i + 1
                , @d    = NULL
        FROM    sys.syslanguages
        WHERE   langid  = @i
    
        SET LANGUAGE @l
    
        SET     @d  = @dc1
        PRINT   @d
        SET     @d  = @dc2
        PRINT   @d
        PRINT   '-----------------------------'
    
    END
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Apr 2007
    Posts
    183
    I usually work with dates without the hyphens, but for clarity (and conformation to ISO 8601 standard http://en.wikipedia.org/wiki/ISO_8601), I sometimes use hyphens.
    The likelyhood that someone misinterpret the date being used is also less with this date format than using 10/7/2009.

    Is that July 10th, or October 7th? With 2009-7-10 there is less confusion.
    Last edited by Peso; 07-22-09 at 10:42.
    Microsoft SQL Server MVP

    N 5604'39.26"
    E 1255'05.63"

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    My point, illustrated by the code, is that hyphens fail in about 2/3+ of the language settings. I also use ISO (note the hyphens are optional in that standard however).

    Using British English language setting:
    '2009-07-10' - succeeds
    '2009-07-30' - fails with error
    '20090730' - succeeds

    In order to use hyphens, I have to use the full ISO:
    '2009-07-30T00:00:00'
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Apr 2007
    Posts
    183
    I'll keep that in mind :-)
    Microsoft SQL Server MVP

    N 5604'39.26"
    E 1255'05.63"

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Peso
    I'll keep that in mind :-)


    "A mind is a terrible thing......."


    Please, oh, please don't hurt me hammer....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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