Help with Dates please

[Ian Piper]

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]

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

[Aaron Bertrand - MVP]

> 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...

[Ian Piper]

Thanks Aaron, that works great.

Regards

Ian

"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:OpZWIBMRDHA.2096@TK2MSFTNGP12.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" <ipiper001@NOSPAM.btinternet.com> wrote in message
> news:beci9l$4go$1@hercules.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
> >
> >

>
>

[Bob Barrows]

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