Professional Web Applications Themes

Help with Dates please - Microsoft SQL / MS SQL Server

Hi, In a SQL table I have a field named departure and a date in inserted in it. What is the exact syntax to get only records with a future date (in departure field) please. The SQL data type for departure is 'datetime' I have tried SELECT * FROM dbo.table WHERE Departure_date> ' & date() & ' and I get the error 'syntax error converting datetime from character string' Many thanks in advance Ian...

  1. #1

    Default Help with Dates please

    Hi,

    In a SQL table I have a field named departure and a date in inserted in it.

    What is the exact syntax to get only records with a future date (in
    departure field) please.

    The SQL data type for departure is 'datetime'

    I have tried SELECT *
    FROM dbo.table
    WHERE Departure_date> ' & date() & '
    and I get the error 'syntax error converting datetime from character string'

    Many thanks in advance

    Ian


    Ian Piper Guest

  2. #2

    Default Re: Help with Dates please

    You will need to convert date() into a date format that the SQL Server can
    recognize. The safest format is YYYYMMDD. I would create a function that
    returns a string containing the properly formatted date.

    Without more details as to what you are writing this in (VB? vbscript?) I
    can't provide more details. If it's VB, you don't even need a function: you
    can use the builtin Format function.

    HTH,
    Bob Barrows

    Ian Piper wrote:
    > Hi,
    >
    > In a SQL table I have a field named departure and a date in inserted
    > in it.
    >
    > What is the exact syntax to get only records with a future date (in
    > departure field) please.
    >
    > The SQL data type for departure is 'datetime'
    >
    > I have tried SELECT *
    > FROM dbo.table
    > WHERE Departure_date> ' & date() & '
    > and I get the error 'syntax error converting datetime from character
    > string'
    >
    > Many thanks in advance
    >
    > Ian

    Bob Barrows Guest

  3. #3

    Default Re: Help with Dates please

    > You will need to convert date() into a date format that the SQL Server can
    > recognize. The safest format is YYYYMMDD. I would create a function that
    > returns a string containing the properly formatted date.
    Well, you don't really need to do this... the date doesn't need to come from
    the application; SQL Server knows what the current date is, so you can avoid
    these formatting issues altogether...


    Aaron Bertrand - MVP Guest

  4. #4

    Default Re: Help with Dates please

    Thanks Aaron, that works great.

    Regards

    Ian

    "Aaron Bertrand - MVP" <aaronTRASHaspfaq.com> wrote in message
    news:OpZWIBMRDHA.2096TK2MSFTNGP12.phx.gbl...
    > If by "future date" you mean any date/time with a date greater than today,
    > then
    >
    >
    > SELECT * FROM dbo.table WHERE
    > Departure_date >= DATEADD(DAY, 1, {fn CURDATE()})
    >
    >
    > "Ian Piper" <ipiper001NOSPAM.btinternet.com> wrote in message
    > news:beci9l$4go$1hercules.btinternet.com...
    > > Hi,
    > >
    > > In a SQL table I have a field named departure and a date in inserted in
    > it.
    > >
    > > What is the exact syntax to get only records with a future date (in
    > > departure field) please.
    > >
    > > The SQL data type for departure is 'datetime'
    > >
    > > I have tried SELECT *
    > > FROM dbo.table
    > > WHERE Departure_date> ' & date() & '
    > > and I get the error 'syntax error converting datetime from character
    > string'
    > >
    > > Many thanks in advance
    > >
    > > Ian
    > >
    > >
    >
    >

    Ian Piper Guest

  5. #5

    Default Re: Help with Dates please

    Aaron Bertrand - MVP wrote:
    >> You will need to convert date() into a date format that the SQL
    >> Server can recognize. The safest format is YYYYMMDD. I would create
    >> a function that returns a string containing the properly formatted
    >> date.
    >
    > Well, you don't really need to do this... the date doesn't need to
    > come from the application; SQL Server knows what the current date is,
    > so you can avoid these formatting issues altogether...
    Future reference, in case the date to be passed isn't today's date :-)

    Again overlooking the obvious,
    Bob


    Bob Barrows Guest

Similar Threads

  1. Between Dates
    By javman in forum Coldfusion - Advanced Techniques
    Replies: 5
    Last Post: June 7th, 02:50 PM
  2. Dates & SQL
    By smokin_joe in forum Coldfusion Database Access
    Replies: 1
    Last Post: July 29th, 04:56 PM
  3. ASP/VBS Dates Between Dates
    By Sanjay in forum Dreamweaver AppDev
    Replies: 3
    Last Post: April 28th, 06:34 PM
  4. Dates again
    By Clive Moss in forum ASP Database
    Replies: 4
    Last Post: January 14th, 03:35 AM
  5. Dates
    By Support in forum PERL Beginners
    Replies: 0
    Last Post: August 19th, 08:35 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