Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2012
    Posts
    30

    Unanswered: [SQL Server 2008] Problem with Between syntax

    [SQL Server 2008] Problem with Between syntax

    Hi there, hope in your help.

    In SQL server I have the column doTableDate set a Datetime.

    I need extract all rows in on date range and I think use to syntax `Between And`

    If try this version of query I have in output 889 rows all with date 2014-01-03... but I have other records with date 2014-01-04 in column doTableData...

    Code:
    SELECT
    	*
    FROM
    	doTable
    WHERE
    	doTableDate BETWEEN CONVERT (
    		datetime,
    		'03/01/2014 00:00:00',
    		103
    	)
    AND CONVERT (
    	datetime,
    	'04/01/2014 00:00:00',
    	103
    );
    If try this version I don't have output no record, why?
    The syntax `Between And` not working in SQL Server?

    Can you help me?
    Thank you in advance.

    Code:
    SELECT
    	*
    FROM
    	doTable
    WHERE
    	doTableDate BETWEEN CONVERT (
    		datetime,
    		'03/01/2014 00:00:00',
    		103
    	)
    AND CONVERT (
    	datetime,
    	'03/01/2014 00:00:00',
    	103
    );

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    When it comes to dates, always use a standardised format i.e. YYYY-MM-DD hh:mm:ss.nnn

    So your query will become
    Code:
    SELECT *
    FROM   doTable
    WHERE  doTableDate BETWEEN '2014-03-01' AND '2014-04-01'
    Personally I *hate* the BETWEEN operator. I would always advocate writing your queries using >= and <= (which is equival
    Code:
    SELECT *
    FROM   doTable
    WHERE  doTableDate >= '2014-03-01'
    AND    doTableDate <= '2014-04-01'
    Lastly, you're forgetting about the time portions in your last query! You're asking for everything between 00:00 on the 1st to 00:00 on the 1st i.e. that *exact* moment.
    I'm guessing you are really wanting everything on that day.
    Code:
    SELECT *
    FROM   doTable
    WHERE  doTableDate >= '2014-03-01'
    AND    doTableDate <  '2014-03-02'
    So that's everything from 00:00 on the first up to, but not including 00:00 on the second!
    George
    Home | Blog

  3. #3
    Join Date
    May 2015
    Posts
    1

    convert column also

    SELECT
    *
    FROM
    doTable
    WHERE
    CONVERT (datetime,doTableDate,103) BETWEEN CONVERT (
    datetime,
    '03/01/2014 00:00:00',
    103
    )
    AND CONVERT (
    datetime,
    '03/01/2014 00:00:00',
    103
    );

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Converting the column will invalidate any indexes that happen to be on that date field.

    And oh, George (not often I get to tweak you on something in your code). You have mistaken the date format required (although that is a good argument for your advocating YYYY-MM-DD). Format 103 is British/French format or DD/MM/YYYY. Still, your assessment of the problem looks to be correct. Setting 2014-01-04 as the top date will exclude anything not exactly at midnight.

  5. #5
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    You do not understand how temporal data works in SQL. You are still back in the old Sybase/UNIX and COBOL mindset. Assume the Lotus position and empty the old tea from your cup, so you may drink new tea.

    You put keywords on single lines to mimic punch cards. We did this for re-use. It was a bitch to find an available keypunch machine back in the old days. I will bet you never saw a punch card at work.

    Today, we lead each line of code with a keyword, followed by the expected relatd syntax elements. I have a whole book SQL Style that goes into the horizontal and vertical eye movements, readabilty, etc. Get a SQL Pretty Printer and follow Mother Celko's rules. I compiled the research for 2 years at AIRMICS; good code formating can save 8% to 12% off maintenance time.

    Putting the meta-data “table” (or -tbl”, etc) in a table name is so bad it has a name -- “tibble” -- we get a laugh out of it. In the old days, FORTRAN I and FORTRAN II cast variables that began with the letters I thru N as INTEGER; everything else was a float. In 1960's BASIC $ marked strings. Many of the 1950's operating systems and packages required affixes for the hardware.

    Modern compilers (post 1975) and software do not do this any more, but this weird skeuomorphism lives on in bad coding practices and weakly typed languages (now out of favor).

    We have a DATE data type in T-SQL. As usual, it arrived decades later than other SQL products, but it is there and you need to use it. As per ANSI/ISO Standard SQL, the only display format is ISO-8601, which means “yyyy-mm-dd”; T-SQL unfortunately has the legacy (aka Family Curse) of having an insane, inconsistent list of national hillbilly displays.

    You seem to have picked the worst (you cannot sort on the display string, the month and day fields [sidebar: in SQL a field is one of the {YEAR, DAY, MONTH, HOUR, MINUTE, SECOND} and nothing like a column] are ambiguous, the slashes conflict with other ISO standards, etc.

    The old Sybases CONVERT() strign function is pure COBOL mindset. That languages treats temporal data as strings. In COBOL, the program is monolithic and not a tiered architecture like SQL. Retrieval, computations, and display formatting are all in the same code module! CONVERT(), DATETIME family, and MONEY family are relics of those by-gone days which mimic the PICTURE clauses in the COBOL language DATA DIVISION.

    In a tiered architecture, we have input layers, presentation layers, computational layers, report servers, etc.

    Assuming “foobar_date” is declared DATE, and you want the month of March, then you can write:

    SELECT <<column list>>
    FROM << valid ISO-11179 table name >>
    WHERE foobar_date
    BETWEEN CAST('2014-03-01' AS DATE)
    AND CAST('2014-03-31' AS DATE);

    The BETWEEN now works the way you expect. You got a gig for SELECT * and the explicit CAST() is redundant but nice documentation.

Posting Permissions

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