Professional Web Applications Themes

Between Todays Date - Microsoft SQL / MS SQL Server

Hi all, Quick question, I can't find my SQL book and its driving me mad. I have a StartDate field and a FinishDate field in my table and I need to know if Todays Date is between or equal to the above fields. I tried (GetDate() Between StartDate and FinishDate) but not working. What am I doing wrong? MC...

  1. #1

    Default Between Todays Date

    Hi all,

    Quick question, I can't find my SQL book and its driving me mad.

    I have a StartDate field and a FinishDate field in my table and I need to
    know if Todays Date is between or equal to the above fields.

    I tried (GetDate() Between StartDate and FinishDate) but not working. What
    am I doing wrong?

    MC


    Mark@simplydavinci.com Guest

  2. #2

    Default Re: Between Todays Date

    com (com) writes: 

    The mistake you are making is to forget that getdate() includes a time
    portion as well.

    convert(char(8), getdate(), 112) BETWEEN StartDate and FinishDate

    would be better.

    I am here assuming that StartDate and FinishDate have their time portions
    as 00:00:00.000.


    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  3. #3

    Default Re: Between Todays Date

    I would suggest not to retype you data into a char as SQL will type back to
    a datetime in most cases or may type the others to a char which adds more to
    the overhead.

    I find the best way to strip the time to 00:00:00.000 is like so

    DATEADD(d,DATEDIFF(d,0,GETDATE()),0)

    so

    DATEADD(d,DATEDIFF(d,0,GETDATE()),0) BETWEEN StartDate and FinishDate


    "Erland Sommarskog" <se> wrote in message
    news:0.0.1... [/ref]
    to [/ref]
    What 
    >
    > The mistake you are making is to forget that getdate() includes a time
    > portion as well.
    >
    > convert(char(8), getdate(), 112) BETWEEN StartDate and FinishDate
    >
    > would be better.
    >
    > I am here assuming that StartDate and FinishDate have their time portions
    > as 00:00:00.000.
    >
    >
    > --
    > Erland Sommarskog, SQL Server MVP, se
    >
    > Books Online for SQL Server SP3 at
    > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/ref]


    James Guest

Similar Threads

  1. Add 5 week days to todays date
    By surfguy in forum Coldfusion - Getting Started
    Replies: 1
    Last Post: April 15th, 07:00 PM
  2. display records >(higher) than todays date?
    By Don Pacino in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 28th, 02:58 PM
  3. Replies: 2
    Last Post: February 26th, 11:43 AM
  4. Get todays match...
    By RotterdamStudents in forum PHP Development
    Replies: 3
    Last Post: April 19th, 08:45 PM
  5. Replies: 3
    Last Post: July 23rd, 08:37 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139