Re: Problem with date formatting in SQL command

On Oct 16, 5:31 am, nartla <bruno.bar...@xxxxxxxxxx> wrote:
On 16 oct, 01:24, Chuck Grimsby <c.grim...@xxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

Format the dateTest variable to YYYY-MM-DD format before enclosing it
in the single quotes:

strSQL = "SELECT ID_PRX, PRX_LIB FROM PRX_TABLE " & _
"WHERE ID_PROJECT = 1000 AND ID_CYCLE <>2 AND " & _
"PRX_END_DATE >= '" & Format(dateTest, "YYYY-MM-DD") & "'*

If there's also a time value in dateTest, use "YYYY-MM-DD HH:NN:SS".

Hello,

Thank you for your answer.

If I just modify the command the way you suggested it and run the app,
I got a message about 'overflow', obviously because dateTest =
"20071016"

So I tried to make the following changes :

dateTest = CStr(Year(Now)) & "-" & CStr(Month(Now)) & "-" &
CStr(Day(Now)) ' dateTest now is "2007-10-16"
strSQL = "SELECT ID_PRX, PRX_LIB FROM PRX_TABLE " & _
"WHERE ID_PROJECT = 1000 AND ID_CYCLE <>2 AND " & _
"PRX_END_DATE >= '" & Format(dateTest, "YYYY-MM-DD") & "'

but i'm still having the same old 3464 error message.

It seems that Access still does not understand that the value after
"PRX_END_DATE >= '" is supposed to be a date, but I don't find a way
to make it work ...
I even tried with "PRX_END_DATE >= CAST(" & dateTest & " AS
DATETIME)" which is supposed to be the right syntax for SQL Server,
but still not working ...

Surely
"PRX_END_DATE >= CAST(" & dateTest & " AS
DATETIME)"
would generate a syntax error, or ask SQL to Cast the integer 20071016
as a date, and something like

CAST ('2007-10-16' AS DateTime)

or

CAST ('" & dateTest & "' AS DateTime)
single quote, double quote & dateTest & double quote, single quote
would be needed?

I use CAST ('2007-10-16' AS DateTime) extensively with no problems.
Then again I don't use ODBC, so this may not be applicable.




.

Relevant Pages

  • Re: DateTime, variants and double
    ... SQL Server stores them as paired integers - see BOL ... The table has a field of type DateTime which for various reasons I ... SELECT CAST AS FLOAT) ... This email account is my spam trap so I ...
    (microsoft.public.data.ado)
  • Re: DateTime, variants and double
    ... The table has a field of type DateTime which for various reasons I ... SELECT CAST AS FLOAT) ... btw I'm using SQL Server 2000 and VC++ 6 ... This email account is my spam trap so I ...
    (microsoft.public.data.ado)
  • Re: handling apostrophe
    ... replace each single quote with two ... single quotes before submitting it to SQL server. ... use command and parameter objects ... > How can i feed it in the routine if i am only a user of that routine.. ...
    (microsoft.public.sqlserver.programming)
  • Re: General Question: Single Quotes in Text Fields
    ... StoredPrcedure in the SQL server, when single quote involved. ... Good Luck ... to morph apostrophes into some other character or just delete them ...
    (microsoft.public.access.modulesdaovba)
  • Re: SQL query fails
    ... > that is acceptable to Oracle, SQL Server, and Access. ... > this requires using the single quote as the literal string delimiter as well ... > thise case determines it to be the single quote. ...
    (microsoft.public.access.queries)