Only the date in a datetime 2

[GoneAccessCrazy]

Is there a way to select only the date of a datetime column,
I was using the left(date1,10) but when the system date format is other that MM/dd/yyyy y end up getting part of the time.
e.g.
Select left(date1,10) as dateBegin, right(date1,8) AS hourBegin ...

when date is MM/dd/yyyy
I get 02/31/2002 10:00:00
but when the date format is M/d/yy
I get 2/31/02 10 10:00:00

GAC

 

[gol4]

one of many ways
format(datetime,"mmddyyyy&quot

 

[GoneAccessCrazy]

Hmmmm, that works inside Access, but not in my Java application, do you know something else?

this query
SELECT Usuario.Login, Format([FechaHoraSesion],"dd/MM/yyyy&quot AS Fecha, Format([FechaHoraSesion],"hh:mm:ss&quot AS Hora, [MontoTAlquiler]+[MontoTProducto]+[MontoMovIngreso]-[MontoMovSalida] AS MontoSistema, Caja.MontoReal, Caja.Observacion FROM Usuario, Caja WHERE Usuario.IdUsuario = Caja.IdUsuario order by Caja.FechaHoraSesion, Usuario.Login;

returns this message... (in the applic)
Error: [Microsoft][Controlador ODBC Microsoft Access] Pocos parámetros. Se esperaba 2.
Translation ==> Too few parameters. 2 expected.

 

[cmmrfrds]

How do you connect to the Access mdb from the Java application? Are you using an OLE-DB provider? What does your connection and recordset objects contain.

 

[cmmrfrds]

How do you connect to the Access mdb from the Java application? Are you using an OLE-DB provider? What does your connection and recordset objects contain.

Did you try.
Format([FechaHoraSesion],"mm/dd/yyyy&quot

Are you using a Spanish version of Access?
Date and Hour are reserved words in Access English version is that also true about Fecha and Hora in Spanish version.

 

[GoneAccessCrazy]

I'm connecting with the default JDBC-ODBC bridge in JDK1.3.1
something like this...

Class.forName(sDriver);
System.out.println("Opening db connection"
connection = DriverManager.getConnection(sUrlBD, sUser, sPassword);

where sDriver=sun.jdbc.odbc.JdbcOdbcDriver
and URL=jdbc\dbc\:bdCabiNet

Yes, I've tried Format([FechaHoraSesion],"mm/dd/yyyy&quot and it doesn't work and yes, I'm using a Spanish version of Access but I don't think that's the problem.
Maybe the JDBC-ODBC bridge don't understand the format function.

Can you imagine another way to take only the date part leaving out the time?

 

[gol4]

yes a few more, but not sure if it will work in your situation either

?datepart("m",date)&"/" datepart("d",date)& "/"& datepart("yyyy",date)
8/5/2002

?dateserial(year(date),month(date),day(date))
8/5/2002

? month(date)&"/"& day(date) &"/"& year(date)
8/5/2002

 

[cmmrfrds]

It appears the driver does not support all the Access (Jet) syntax.

Maybe this will help.
The datetime is stored in Access similiar to the datetime in other relational databases. It is stored as a decimal number with the integer part being days and the decimal part being hours on a 24 hours in a day basis. Access uses 12/31/1899 as the starting point for the days part of the field. So, if the date is converted to an integer (access long) then the number will be the days since 12/31/1899. Example, myvar = Int(thedate). This would strip off the time part of the datetime.

Maybe this will work, at least it is a different function.
Dim myvar as long 'move your comparison date into this
myvar = Int(yourcomparedate)

select * from yourtable where Int(dateintable) = myvar

 

[GoneAccessCrazy]

Thanks for your help...

the format stuff worked in Java with 'dd/MM/yyyy' instead of "dd/MM/yyyy"...

I'm dumb or what!!!

Again, thanks for all
GAC