[VB2010] - Operazioni con le date

[lucausa75]

Salve ragazzi,

mi sorge un dubbio.

La mia applicazione salva su un database di Access alcuni valori tra cui una data che a livello di TextBox formatto nel formato (yyyy/mm/dd hh.mm.ss.)

Quando però visualizzo il dato aprendo il database questa data appare nel formato dd/mm/yyyy hh.mm.ss cioè per capirci a livello di TextBox il formato è 2010/11/19 18.02.47 ma nel database visualizzo 19/11/2010 18.02.47

Sicuramente la visualizzazione su Access dipende dalle impostazioni internazionali di sistema e quindi mi chiedo se questo database su un sistema diverso dal mio con impostazioni internazionali ad esempio americane visualizzerà correttamente le date anche a livello di operazioni con le date.

Non vorrei che se dovessi fare una sottrazione di date create su due sistemi con opzioni internazionali ottengo valori strani...

Mi chiarite il dubbio?
Grazie

[MarcoGG]

In Access, come in qualsiasi altro DB il tipo DATA "non esiste".
A livello di memorizzazione esiste solo un numero. Nel caso di Access è un Long. Perciò il tipo Data/Ora, quando ad esempio apri in visualizzazione diretta una tabella Access, in realtà è già una rappresentazione "astratta" di quanto in realtà è scritto nel DB.
Se carichi una data da Access in VB.NET, tranquillo. Il valore è sempre quello, e non dipende da impostazioni o da formattazioni... ;)

[lucausa75]

Grazie per il tuo prezioso supporto.

Ti chiedo adesso come sviluppare questa query:

SELECT * FROM [MiaTabella] WHERE Creazione = 'Data di oggi'

Calcola che il campo creazione è di tipo data estesa (Es:. 20/11/2010 11.02.54) e data di oggi deve essere di tipo classico (Es:. 20/11/2010)

Grazie ancora!

[MarcoGG]

Anzitutto una correzione a quanto ho scritto : nelle nuove versioni di Access ( credo dalla 2000 in poi... ) il tipo data viene memorizzato come valore numerico Double ( non più Long, quindi ).

Il discorso è abbastanza semplice.
Poniamo di avere una Tabella con 2 campi :
- campo_data_generica : formato data generica - aaaa/mm/gg hh/mm/ss
- campo_data_in_cifre : formato data in cifre - aaaa/mm/gg

1. Creo una data estesa in VB e faccio la INSERT in campo_data_generica :
Dim D As New DateTime(2010, 11, 22, 0, 0, 0)

2. Creo una data semplice e faccio la INSERT in campo_data_in_cifre :
Dim D As New DateTime(2010, 11, 22)

3. Creo una data estesa in VB e faccio la INSERT in campo_data_generica :
Dim D As New DateTime(2010, 11, 22, 12, 30, 45)

Se voglio conoscere il reale valore numerico della data in Access, basta usare il metodo ".ToOADate()" ( To-Ole-Automation-Date ), che guarda caso restituisce un Double :

1. SELECT restituisce 40504

2. SELECT restituisce sempre 40504

3. SELECT restituisce 40504,521354......

Perciò inserire o leggere una data estesa con ore/min/sec = 0/0/0 equivale a inserire/leggere una data semplice, senza specificare la parte oraria.
Bisognerebbe inoltre notare che nel Double restituito da .ToOADate() ci sono anche i millisecondi...

Detto questo, agisci di conseguenza... ;)

[lucausa75]

...ma perchè non è possibile utilizzare una funzione di conversione all'interno della Select?
Quì c'è un esempio interessante: http://www.sqlusa.com/bestpractices/datetimeconversion/

Cioè poniamo il caso di avere una tabella così fatta:

http://img441.imageshack.us/img441/8527/90219750.th.png (http://img441.imageshack.us/i/90219750.png/)

Vorrei realizzare una Select in modo da visualizzare solo i record con DataCreazione uguale a 22/11/2010:

SELECT * FROM [MiaTabella] WHERE DataCreazione = 'Data di oggi'

[lucausa75]

...alla fine senza utilizzare alcuna conversione ho ragionato sull' intervallo di durata di una data ed ecco la mia stringa SQL:

SELECT * FROM [" & TB & "] WHERE Creazione >= #" & Today & " 00.00.00# AND Creazione <= #" & Today & " 23.59.59#

[MarcoGG]

...ma perchè non è possibile utilizzare una funzione di conversione all'interno della Select?
Quì c'è un esempio interessante: http://www.sqlusa.com/bestpractices/datetimeconversion/


Quelle sono funzioni di conversione per il TSql di Sql Server.
L'Sql di Access non è detto che le digersica. Che io sappia CONVERT non è supportato. DATEPART restituisce stringhe, quindi si ricade nel problema delle impostazioni locali, formattazioni...
Se mi devo gestire separatamente Anno, Mese e Giorno a questo punto uso 3 OledbParameters, non certo 3 direttive DATEPART concatenate. :nonsifa:


Cioè poniamo il caso di avere una tabella così fatta:

http://img441.imageshack.us/img441/8527/90219750.th.png (http://img441.imageshack.us/i/90219750.png/)

Vorrei realizzare una Select in modo da visualizzare solo i record con DataCreazione uguale a 22/11/2010:

SELECT * FROM [MiaTabella] WHERE DataCreazione = 'Data di oggi'


Due soluzioni possibili :

1. Come già detto, confronto la data "a pezzi", con 3 Parameters per Anno, Mese, Giorno. Forse un po' prolissa, ma sicura al 100%.

2. Uso la Function Access DATEVALUE(). Penso che al 99% non dia sorprese, con qualsiasi impostazione/formattazione :
Dim dataConfronto As New DateTime(2010, 11, 22)

Dim strSql As String = "SELECT DataCreazione FROM T_Libri WHERE DATEVALUE(DataCreazione)=@dataConfronto"

Using CN As New OleDb.OleDbConnection(strCN)
Using CMD As New OleDb.OleDbCommand(strSql, CN)
'Assegnazione Parametri :
CMD.Parameters.Add("@dataConfronto", OleDb.OleDbType.Date)
CMD.Parameters("@dataConfronto").Value = dataConfronto

'SELECT
CN.Open()
Dim RDR As OleDb.OleDbDataReader = CMD.ExecuteReader
While RDR.Read
'...
End While
End Using
End Using

Pagare birra e lasciare un grosso "Grazie" in bacheca sulla mia pagina FB. :D

[MarcoGG]

...alla fine senza utilizzare alcuna conversione ho ragionato sull' intervallo di durata di una data ed ecco la mia stringa SQL:

SELECT * FROM [" & TB & "] WHERE Creazione >= #" & Today & " 00.00.00# AND Creazione <= #" & Today & " 23.59.59#

No, non la userei proprio. :D
Molto meglio con DATEVALUE(). ;)

[lucausa75]

No, non la userei proprio. :D
Molto meglio con DATEVALUE(). ;)

Proverò col DateValue e dopo ti offrirò un birra...virtuale! :D :cincin:

[lucausa75]

...con DateValue nel mio caso specifico mi da errore "Data type mismatch in criteria expression"

SELECT * FROM [" & TB & "] WHERE DATEVALUE(DataCreazione) = #" & Today & "#"

:confused:

[MarcoGG]

...con DateValue nel mio caso specifico mi da errore "Data type mismatch in criteria expression"

SELECT * FROM [" & TB & "] WHERE DATEVALUE(DataCreazione) = #" & Today & "#"

:confused:

Vedo che sei ancora bloccato lì. Il mio codice utilizza i Parameters.
Credo sia dovuto al fatto che DATEVALUE restituisce una stringa, NON una data.
Il fatto è che con il mio sistema, il Parameter beneficia del casting implicito di VB, mentre concatenando come fai tu, NO.

Un motivo in più per passare ai Parameters. ;)

[lucausa75]

Vedo che sei ancora bloccato lì. Il mio codice utilizza i Parameters.
Credo sia dovuto al fatto che DATEVALUE restituisce una stringa, NON una data.
Il fatto è che con il mio sistema, il Parameter beneficia del casting implicito di VB, mentre concatenando come fai tu, NO.

Un motivo in più per passare ai Parameters. ;)


OK, prossimo studio sarà passare ai Parameters
Quindi per adesso penso vada bene la soluzione adottata dato che non faccio uso ancora dei Parameters?

SELECT * FROM [" & TB & "] WHERE Creazione >= #" & Today & " 00.00.00# AND Creazione <= #" & Today & " 23.59.59#

[MarcoGG]

OK, prossimo studio sarà passare ai Parameters
Quindi per adesso penso vada bene la soluzione adottata dato che non faccio uso ancora dei Parameters?

SELECT * FROM [" & TB & "] WHERE Creazione >= #" & Today & " 00.00.00# AND Creazione <= #" & Today & " 23.59.59#

No. Inizia da subito.
Le uniche situazioni in cui si può tollerare la brutale concatenazione sono quelle estremamente semplici, come questa :
"SELECT * FORM Tabella WHERE ID=" & numero

Già con una semplice data può creare problemi, fino a vere e proprie catastrofi su stringhe Sql complesse.

Quella soluzione "intermedia" non mi piace per niente.

Poi, uomo avvisato... ;)

[lucausa75]

No. Inizia da subito.
Le uniche situazioni in cui si può tollerare la brutale concatenazione sono quelle estremamente semplici, come questa :
"SELECT * FORM Tabella WHERE ID=" & numero

Già con una semplice data può creare problemi, fino a vere e proprie catastrofi su stringhe Sql complesse.

Quella soluzione "intermedia" non mi piace per niente.

Poi, uomo avvisato... ;)

:ave:

[lucausa75]

Salve ragazzi,
riporto su questo argomento perchè volevo capire due cosette riguardo Access:

1) Utilizzando le query il formato delle date deve essere di tipo MM/dd/yyyy (Esempio 8 Dicembre 2010 diventa 12/08/2010)?

2) Se la data comprende anche la parte oraria questa deve essere separata dal punto (.) o dal due punti (:)?

Vi faccio questa domanda perchè ho problemi nell'effettuare delle query di confronto di date all'interno di questo database (http://musicbylucausa75.altervista.org/Biblio.mdb) che vi invito a fare e a correggere :D

Query 1 (Non da alcun risultato ma filtrando "a vista" i record ci sono):
SELECT [Elenco Libri].*
FROM [Elenco Libri]
WHERE ((([Elenco Libri].Created)=#11/26/2010#))

Query 2 (I record ci sono ma non vengono esclusi quelli con Created superiore al 26-Nov-2010):
SELECT [Elenco Libri].*
FROM [Elenco Libri]
WHERE ((([Elenco Libri].Created)>#11/26/2010#))

Query 3 (Visualizza i record correttamente):
SELECT [Elenco Libri].*
FROM [Elenco Libri]
WHERE ((([Elenco Libri].Created)>=#11/26/2010#))

Query 4 (Visualizza i record correttamente):
SELECT [Elenco Libri].*
FROM [Elenco Libri]
WHERE ((([Elenco Libri].Created)<#11/26/2010#))

Query 5 (I record ci sono ma non vengono esclusi (invece ci debbono essere come da query) quelli con Created superiore al 26-Nov-2010):
SELECT [Elenco Libri].*
FROM [Elenco Libri]
WHERE ((([Elenco Libri].Created)<=#11/26/2010#))

Query 6 (I record ci sono ma non vengono esclusi quelli con Created diversa al 26-Nov-2010):
SELECT [Elenco Libri].*
FROM [Elenco Libri]
WHERE ((([Elenco Libri].Created)<>#11/26/2010#))

Debbo utilizzare qualche accorgimento particolare?

Grazie

[MarcoGG]

1) Utilizzando le query il formato delle date deve essere di tipo MM/dd/yyyy (Esempio 8 Dicembre 2010 diventa 12/08/2010)?

2) Se la data comprende anche la parte oraria questa deve essere separata dal punto (.) o dal due punti (:)?
...
...
Debbo utilizzare qualche accorgimento particolare?


1) Sì. Access vuole le date ( se passate come stringa ) così : "#MM/DD/YYYY#"

2) "#MM/DD/YYYY hh:mm:ss#". Al momento non so se accetti anche il punto come separatore orario, perchè è davvero una vita che non inserisco date in questo modo... ;)

Accorgimenti ?
- MAI usare spazi nei nomi di qualunque entità di un DataBase.
SELECT [Elenco Libri].*
FROM [Elenco Libri]
WHERE ((([Elenco Libri].Created)=#11/26/2010#))
Davvero, seriamente questa potrebbe stare in un'enciclopedia come Esempio su come NON agire.
E poi sei costretto ad usare pure un mucchio di parentesi :
Elenco_Libri, ElencoLibri, ma MAI [Elenco Libri] !

- Usare i parameters ( ultima volta nella vita che te lo ripeto, giuro :D ):
Prendi solo la prima query e ripensala così :
SELECT *
FROM Elenco_Libri
WHERE Created=@parametroData
Secondo te quale è meglio ?
;)

[lucausa75]

1) Sì. Access vuole le date ( se passate come stringa ) così : "#MM/DD/YYYY#"

2) "#MM/DD/YYYY hh:mm:ss#". Al momento non so se accetti anche il punto come separatore orario, perchè è davvero una vita che non inserisco date in questo modo... ;)

Accorgimenti ?
- MAI usare spazi nei nomi di qualunque entità di un DataBase.
SELECT [Elenco Libri].*
FROM [Elenco Libri]
WHERE ((([Elenco Libri].Created)=#11/26/2010#))
Davvero, seriamente questa potrebbe stare in un'enciclopedia come Esempio su come NON agire.
E poi sei costretto ad usare pure un mucchio di parentesi :
Elenco_Libri, ElencoLibri, ma MAI [Elenco Libri] !

- Usare i parameters ( ultima volta nella vita che te lo ripeto, giuro :D ):
Prendi solo la prima query e ripensala così :
SELECT *
FROM Elenco_Libri
WHERE Created=@parametroData
Secondo te quale è meglio ?
;)


Questa SELECT [Elenco Libri].*
FROM [Elenco Libri]
WHERE ((([Elenco Libri].Created)=#11/26/2010#)) è la query che viene fuori direttamente da Access e cmq qualcosa mi dice che la soluzione migliore è sicuramente la tua :D

Ma perchè la @ dopo parametroData?
Cmq io mi riferivo direttamente ad Access nella sezione Query

[MarcoGG]

Questa ... è la query che viene fuori direttamente da Access e cmq qualcosa mi dice che la soluzione migliore è sicuramente la tua :D

Ma perchè la @ dopo parametroData?

Le query generate da Access non sono certo oro colato, così come non è oro colato un codice VBA generato dal Macro Recorder...

La @ è una convenzione generalmente accettata, che a me piace molto.
Di solito la variabile-parametro prende il nome dal campo che deve mappare. Anteponendo un carattere speciale si evitano errori e inoltre tutto risulta più leggibile :

...
Dim strSql As String = "SELECT * FROM nomeTabella WHERE nome=@nome"
...
...
CMD.Parameters.Add("@nome", OleDb.OleDbType.VarChar)
...

Ma potrei anche :
...
Dim strSql As String = "SELECT * FROM nomeTabella WHERE nome=§nome"
...
...
CMD.Parameters.Add("§nome", OleDb.OleDbType.VarChar)
...

L'importante è NON usare caratteri propri della sintassi VB.
Se usi dollaro ( $nome ) : Errore. ;)

[lucausa75]

Le query generate da Access non sono certo oro colato, così come non è oro colato un codice VBA generato dal Macro Recorder...

La @ è una convenzione generalmente accettata, che a me piace molto.
Di solito la variabile-parametro prende il nome dal campo che deve mappare. Anteponendo un carattere speciale si evitano errori e inoltre tutto risulta più leggibile :

...
Dim strSql As String = "SELECT * FROM nomeTabella WHERE nome=@nome"
...
...
CMD.Parameters.Add("@nome", OleDb.OleDbType.VarChar)
...

Ma potrei anche :
...
Dim strSql As String = "SELECT * FROM nomeTabella WHERE nome=§nome"
...
...
CMD.Parameters.Add("§nome", OleDb.OleDbType.VarChar)
...

L'importante è NON usare caratteri propri della sintassi VB.
Se usi dollaro ( $nome ) : Errore. ;)


Ottima spiegazione ma se volessi creare una query quelle indicate nel mio post internamente da access come dovrei fare?

Come scritto pare abbiaano dei problemi...

[MarcoGG]

Sicuramente questa in Access è corretta :

SELECT *
FROM nome_tabella
WHERE campo_data=#11/26/2010#

Agisci di conseguenza anche per le altre. E controlla anche l'eventuale parte oraria di campo_data... ;)

[lucausa75]

Sicuramente questa in Access è corretta :

SELECT *
FROM nome_tabella
WHERE campo_data=#11/26/2010#

Agisci di conseguenza anche per le altre. E controlla anche l'eventuale parte oraria di campo_data... ;)

Ho provato questa query sul database access di cui al post #15 ma non da alcun record; sicuramente il problema è dovuta alla parte oraria

[MarcoGG]

Ok, faccio un esempio definitivo così tagliamo la testa al toro !

Poniamo di avere una tabella Access con un campo "campo_data".
campo_data è del tipo "data generica", ossia data + parte oraria.
Una data qualsiasi verrà visualizzata in questo campo nel formato :
DD/MM/YYYY hh.mm.ss

Ora, inserisco 2 date :
26/11/2010 12.05.32
26/11/2010 00.00.00

QUERY1:
SELECT *
FROM nome_tabella
WHERE campo_data=#11/26/2010#
Restituisce solo il record con la seconda data, perchè la parte oraria è ininfluente.
Siamo nel caso in cui una data con parte oraria 00:00:00 coincide ad una data senza parte oraria.

Se voglio una Query che prenda in esame le date senza considerarne la parte oraria, devo usare la funzione DATEVALUE().
QUERY2:
SELECT *
FROM nome_tabella
WHERE DATEVALUE(campo_data)=#11/26/2010#
Restituisce entrambi i record. ;)

Una volta capito questo, le date in Access non hanno più misteri. :D

[lucausa75]

Ok, faccio un esempio definitivo così tagliamo la testa al toro !

Poniamo di avere una tabella Access con un campo "campo_data".
campo_data è del tipo "data generica", ossia data + parte oraria.
Una data qualsiasi verrà visualizzata in questo campo nel formato :
DD/MM/YYYY hh.mm.ss

Ora, inserisco 2 date :
26/11/2010 12.05.32
26/11/2010 00.00.00

QUERY1:
SELECT *
FROM nome_tabella
WHERE campo_data=#11/26/2010#
Restituisce solo il record con la seconda data, perchè la parte oraria è ininfluente.
Siamo nel caso in cui una data con parte oraria 00:00:00 coincide ad una data senza parte oraria.

Se voglio una Query che prenda in esame le date senza considerarne la parte oraria, devo usare la funzione DATEVALUE().
QUERY2:
SELECT *
FROM nome_tabella
WHERE DATEVALUE(campo_data)=#11/26/2010#
Restituisce entrambi i record. ;)

Una volta capito questo, le date in Access non hanno più misteri. :D

Perfetto!:D

[lucausa75]

...perchè questa query

SELECT TOP 50 ElencoLibri.*
FROM ElencoLibri ORDER BY Created DESC

mi restituisce più di 50 records?

Non mi dire che debbo mettere anche quì il DATEVALUE...

[MarcoGG]

No, DATEVALUE non c'entra nulla. :D

Il fatto è che il predicato TOP non consente di scegliere tra valori uguali.
Sicuramente restituisce più di 50 records perchè nel campo specificato per ORDER BY hai 2 o più valori identici, e TOP, non sapendo quale scegliere, li prende tutti. ;)

[lucausa75]

No, DATEVALUE non c'entra nulla. :D

Il fatto è che il predicato TOP non consente di scegliere tra valori uguali.
Sicuramente restituisce più di 50 records perchè nel campo specificato per ORDER BY hai 2 o più valori identici, e TOP, non sapendo quale scegliere, li prende tutti. ;)

:ave:

ma le date nel mio database hanno anche l'orario e qui siamo sicuri che date uguali non ce ne sono proprio per differenze anche minime di orario.

:confused:

Si potrebbe optare per ORDER BY MioCampoID DESC...

[MarcoGG]

ma le date nel mio database hanno anche l'orario e qui siamo sicuri che date uguali non ce ne sono proprio per differenze anche minime di orario.


Mmm... Proprio sicuro ?


Si potrebbe optare per ORDER BY MioCampoID DESC...


Sì, prendendo un campo che non contiene sicuramente doppioni avresti la sicurezza di avere esattamente 50 records... l'ORDER BY sulle date lo puoi fare su una seconda Query... ;)

[lucausa75]

Questo è il database di esempio: http://musicbylucausa75.altervista.org/Biblio.mdb

[MarcoGG]

Questo è il database di esempio: http://musicbylucausa75.altervista.org/Biblio.mdb

Nessun mistero.
La Query era :
SELECT TOP 50 *
FROM ElencoLibri ORDER BY Created DESC

Io nel Campo "Created" vedo una marea di data/ora tutte uguali :
26/11/2010 20.34.16

Perciò è prevedibile che TOP restituisca un numero maggiore di record.