Select Where confronto DatetTime [RISOLTO]

di
By65Franco
il
15 risposte
By65Franco
By65Franco
Molto attivo
Iscritto da
apr, 2023
Messaggi:
1022

Select Where confronto DatetTime [RISOLTO]

Salve a tutti 

Esempio:

Mi ritrovo a dover selezionare dei records sulla base di talune colonne di tipo DateTime, inserendo le condizioni nelle Where Condition oppure nei Filtri delle DatagridView etc.. 

Nelle tabelle le colonne che contengono le date le ho definite come tipo dati DateTime (non ho utilizzatozo il DateTime2)

Le variabili a programma le ho definite sempre di tipo DateTime

Ho provato di tutto e non capisco se devo anche tener conto dei millisecondi…
Esempio :

Variabile a programma … 
DateTime dtMydate = DateTime.Now;

Colonna da tabella …
BkDateTimeBackup 

se nella tabella BkDateTimeBackup = 07/08/2023 12:37:12.703

se la variabile  dtMydate = 07/08/2023 12:37:12 

WHERE BkDateTimeBackup = dtMydate 

non restituirà mai dei records corrispondenti… 
Che devo fare,  devo confrontare anche i millisecondi ?  
a me basterebbe confrontare fino ai secondi per eseguire le selezioni, come approcciare correttamente tali confronti tra DateTime ?
Devo convertire nelle Select sia la colonna della tabella che la variabile in formato ‘dd/MM/yyyy hh:mm:ss’ ?

Grazie 

15 Risposte

  • Alka
    Alka
    Utente Famoso
    Iscritto da
    nov, 2014
    Messaggi:
    3076

    Re: Select Where confronto DatetTime [RISOLTO]

    07/08/2023 - By65Franco ha scritto:

    a me basterebbe confrontare fino ai secondi per eseguire le selezioni, come approcciare correttamente tali confronti tra DateTime ?

    Devi usare le apposite funzioni che estraggono parti del campo data/ora per fare gli opportuni confronti oppure, in alternativa, usare dei tipi di dato limitati a data oppure all'ora (se ti interessa solo una di queste).

  • By65Franco
    By65Franco
    Molto attivo
    Iscritto da
    apr, 2023
    Messaggi:
    1022

    Re: Select Where confronto DatetTime [RISOLTO]

    07/08/2023 - Alka ha scritto:

    07/08/2023 - By65Franco ha scritto:

    a me basterebbe confrontare fino ai secondi per eseguire le selezioni, come approcciare correttamente tali confronti tra DateTime ?

    Devi usare le apposite funzioni che estraggono parti del campo data/ora per fare gli opportuni confronti oppure, in alternativa, usare dei tipi di dato limitati a data oppure all'ora (se ti interessa solo una di queste).

    Si certo… capisco

    quindi devo formattare o convertire il tipo dati in un formato che non tenga in considerazione i millisencondi

    Una cosa di questo tipo andrebbe bene come approccio ?

    SELECT [IdBk]
         ,[BkDateTimeBackup]
         ,[BkFullPath]
         ,[BkFileName]
         ,[BkDateTimeEdit]
         ,[BkOperationType]
         ,[BkStatus]
     FROM [dbo].[Tbl_BkOperation]
     WHERE CONVERT(varchar(19), BkDateTimeBackup, 120) = '2023-08-07 14:39:57'

    Quindi la WHERE dovrei scriverla sempre in questo modo ?

     WHERE CONVERT(varchar(19), BkDateTimeBackup, 120) = '2023-08-07 14:39:57'

    (ho visto che filtra correttamente le date in questione)
    La domanda è :  ma è necessario fare solo in questo modo i confronti tra dateTime ?

  • max.riservo
    max.riservo
    Molto attivo
    Iscritto da
    ago, 2016
    Messaggi:
    666

    Re: Select Where confronto DatetTime [RISOLTO]

    Se i microsecondi non servono, la prima soluzione è quella di configurare correttamente il tipo di dato :

    - per me se configuri il tipo come DateTime i microsecondi non vengono memorizzati. Ma se così non fosse puoi sempre impostare che non vuoi i microsecondi  : DateTime(0)

    Dal manuale di mySQL :

    DATETIME[(fsp)]

    A date and time combination. The supported range is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'. MySQL displays DATETIME values in 'YYYY-MM-DD hh:mm:ss[.fraction]' format, but permits assignment of values to DATETIME columns using either strings or numbers.

    An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.

    Automatic initialization and updating to the current date and time for DATETIME columns can be specified using DEFAULT and ON UPDATE column definition clauses, as described in Section 11.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

    La seconda soluzione, se NON è possibile impostare il campo data senza i microsecondi (ma non ci credo) è quella di salvarli a 0.

    Buona fortuna (e buon studio) con mySQL

  • max.riservo
    max.riservo
    Molto attivo
    Iscritto da
    ago, 2016
    Messaggi:
    666

    Re: Select Where confronto DatetTime [RISOLTO]

    La terza soluzione, se si vogliono avere i microsecondi come storicizzazione ma non si vuole impazzire con le query di ricerca sui campi data è quella di salvare i microsecondi in un campo dedicato e di impostarli a zero nel campo DateTime. 

    I due campi vengono poi uniti solo all'occorrenza …

  • By65Franco
    By65Franco
    Molto attivo
    Iscritto da
    apr, 2023
    Messaggi:
    1022

    Re: Select Where confronto DatetTime [RISOLTO]

    07/08/2023 - max.riservo ha scritto:

    La terza soluzione, se si vogliono avere i microsecondi come storicizzazione ma non si vuole impazzire con le query di ricerca sui campi data è quella di salvare i microsecondi in un campo dedicato e di impostarli a zero nel campo DateTime. 

    I due campi vengono poi uniti solo all'occorrenza …

    Ciao, grazie per l'incoraggiamento ;-))

    in questa App deve eseguire dei backup da path locale a remote path o altra path … dipende dall'uso che si vuole fare

    Pertanto mi occorrono le Date dei Backup, cioè quando è stato eseguito un Backup 
    In questo caso salvo la data di inizio operazione del Backup e assumo il DateTime Now

    Per i files che vengono salvati nel backup, memorizzo la data ultima modifica/accesso al file in modo da poter eseguire successivamente dei backup differenziali, cioè solo di quei file che risultano modificati rispetto la data del precedente Backup.

    A seguito delle operazioni di Backup creo uno storico dei files salvati… questo storico lo visualizzo in un datagridview e ho inserito tra gli altri controlli un filtro per Data Operazione Backup. Questo consentirà di analizzare l'esito del backup di quel giorno specifico oppure di tutti i Backup.

    Secondo questo scenario mi è sufficiente salvare le date con il time (DateTime) fino ai secondi nella forma ‘dd/MM/yyyy HH:mm:ss’ dei millisecondi non saprei cosa farne o meglio, non ritengo opportuno e utile portarsi dietro i millisecondi.

    Ma appena sono arrivato a creare il filtro per caricare nel datagridview i backup di solo un giorno a quell'orario, ho trovato subito questo ostacolo a me sconosciuto e dopo tanto impazzire e ricerche nella documentazione, mi sembrava assurdo dover convertire i campi data per un corretto confronto.

    Per esempio, per filtrare e caricare i dati nel datagridview per DataTime selezionata, mi viene fuori una roba di questo tipo e mi sembrava un pò assurdo questo approccio:

    filteredTable = myBackup65DataSet.Tbl_BkOperation.AsEnumerable()
               .Where(row => ((DateTime)row["BkDateTimeBackup"]).ToString("yyyy-MM-dd HH:mm:ss") == dtDateTimeOperation.ToString("yyyy-MM-dd HH:mm:ss"))
               .Skip(startIndex).Take(pageSize).CopyToDataTable();

// update rows datasource datagridview
DataGridBkOperation.DataSource = filteredTable;

    In questo caso aggiorno il DataSet applicando il filtro per DateTime e con Skip e Take gestisco la visualizzazione delle pagine per la navigazione senza caricare l'immensa tabella nel datagrid … in più il pageSize conterrà le enne righe da visualizzare per pagina. Insomma una cosa del genere…

    Allora mi son detto, prima di proseguire meglio chiedere che usare metodi poco ortodossi ;-))

    Grazie

  • A
    asql
    Molto attivo
    Iscritto da
    dic, 2018
    Messaggi:
    515

    Re: Select Where confronto DatetTime [RISOLTO]

    Salve @By65Franco,

    > WHERE [BkDateTimeBackup] BETWEEN '07-08-2023 12:37:12.00' AND '07-08-2023 12:37:12.999'

    successivamente, fortunatamente utilizzi il formato ISO '2023-08-07 14:39:57'

    giusto per ricordare la cosa, visto che stai utilizzando SQL Server, il valore di datetime e' sempre arrotondato a 3 millisecondi, ed in questo senso '07-08-2023 12:37:12.999' sarebbe automaGicamente arrotondato a 12:37:13.000... per trovare il valore massimo del secondo, gestendo i millesimi, devi utilizzare '07-08-2023 12:37:12.997'

    sempre per ricordare la cosa, e sempre in SQL Server, e' sempre consigliabile utilizzare il formato ISO, quindi '2023-08-07 14:39:57', perche' diversamente l'interpretazione del "valore" della data dipende dalle impostazioni internazionali legate all'impostazione di lingua dell'account che esegue la query... nel tuo caso, '07-08-2023 12:37:12.999', utilizzato da un account con localizzazione in inglese, intenderebbe l'8 luglio 2023...

    il formato YYYY-MM-DD invece viene sempre correttamente riconosciuto in quanto tale.

    per il resto, come gia' hai avuto modo di avere conferma, devi effettivamente "accorciare" la data per effettuare operazioni di confronto con una data senza i millisecondi come da te passata come parametro, visto che il valore originale in tabella invece contiene tali valorizzazioni... diversamente, ovviamente, non avresti comparazioni positive per come e quanto da te ricercato...

    questo vale anche per il filtro lato client, visto anche che stai tennicamente operando lato client in c#, filtrando l'intera popolazione dopo averla comunque interamente scaricata lin locale…

    sinceramente, non concordo con quanto indicato da @Max,Riservo circa la separazione della parte dei millisecondi… opinione personale… io manterrei  sempre e comunque il dato “qual'e'”… le funzioni di formattazione/conversione sono fatte apposta per essere utilizzate, anche se sicuramente hanno un peso nel piano di esecuzione, ma sono comunque degli scalari

    salutoni romagnoli
    -- 
    Andrea

  • max.riservo
    max.riservo
    Molto attivo
    Iscritto da
    ago, 2016
    Messaggi:
    666

    Re: Select Where confronto DatetTime [RISOLTO]

    07/08/2023 - asql ha scritto:

    giusto per ricordare la cosa, visto che stai utilizzando SQL Server, il valore di datetime e' sempre arrotondato a 3 millisecondi, ed in questo senso '07-08-2023 12:37:12.999' sarebbe automaGicamente arrotondato a 12:37:13.000... per trovare il valore massimo del secondo, gestendo i millesimi, devi utilizzare '07-08-2023 12:37:12.997'

    Buono a sapersi … tra parentesi (ma non troppo) io ho suggerito qualcosa inerente a mySQL che a quanto pare è fuori luogo (direi che Franco sta usando SQL Server) … sorry.

  • By65Franco
    By65Franco
    Molto attivo
    Iscritto da
    apr, 2023
    Messaggi:
    1022

    Re: Select Where confronto DatetTime [RISOLTO]

    07/08/2023 - asql ha scritto:

    giusto per ricordare la cosa, visto che stai utilizzando SQL Server, il valore di datetime e' sempre arrotondato a 3 millisecondi, ed in questo senso '07-08-2023 12:37:12.999' sarebbe automaGicamente arrotondato a 12:37:13.000... per trovare il valore massimo del secondo, gestendo i millesimi, devi utilizzare '07-08-2023 12:37:12.997'

    Ciao,  …ok, per l'arrotondamento (appena studiato) ho visto tale fatto e diversamente dal DateTime2 che risulterebbe con maggiore precisione, ma che ho evitato di utilizzare visto che è entrato in vigore con le ultime versioni di sqlserver
    Mentre per il tipo Iso … devo meglio approfondire perchè mi entri bene in testa le differenze etc etc … ;-) 

    Grazie per le conferme e precisazioni esaustive che sono molto importanti e che mi danno conferma per quanto approfondito con lo studio.

    Vi chiedo cortesemente una ultima conferma se possibile… per esempio anche nel caso di Where Condition sui filtri per le datagridview conviene sempre passare gli argomenti come Parametri con la @value… ?

    Grazie ancora a tutti ;-))

  • A
    asql
    Molto attivo
    Iscritto da
    dic, 2018
    Messaggi:
    515

    Re: Select Where confronto DatetTime [RISOLTO]

    Salve Franco,

    08/08/2023 - By65Franco ha scritto:

    Vi chiedo cortesemente una ultima conferma se possibile… per esempio anche nel caso di Where Condition sui filtri per le datagridview conviene sempre passare gli argomenti come Parametri con la @value… ?

    brevemente, l'utilizzo dei parametri nell'esecuzione di query e' SEMPRE da consigliare… non gia' per il possibile riutilizzo dei piani di esecuzione da parte di dbms, in quanto questi sono gia' molto smart per conto loro, ma sicuramente per evitare sia banali errori di concatenazione con cast errati o similari, ma soprattutto per evitare problematiche di sicurezza legate a SQL Injection… per quanto mi riguarda, vista la mia esta', anche per forma mentale di opportuna separazione del codice :D

    08/08/2023 - By65Franco ha scritto:


    Mentre per il tipo Iso … devo meglio approfondire perchè mi entri bene in testa le differenze etc etc … ;-) 

    non e' “un tipo”… e' un “formato”… SQL Server archivia le valorizzazioni dei tipi di dato date/datetime/datetime2 in formato numericoproprietario, e le operazioni di confronto con essere vanno solitamente espresse in formato “stringa”… da qui la problematica sopra evidenziata che puo' portare a banali errori sia “fisici” (impossibilita' di convertire la stringa “13/01/2023” in una data, per noi italiani 13 gennaio ma per gli americanti 1 giorno del tredicesimo mese) che logici ("01/10/2023" e' il 1 ottobre OPPURE il 10 gennaio?? dipende dalla localizzazione della login corrente…)

    quindi e' sempre consigliabile utilizzare un formato che non lasci dubbi e sia sempre interpretato nello stesso modo, a prescindere dalle impostazioni di contesto

    salutoni romagnoli
    – 
    Andrea

  • By65Franco
    By65Franco
    Molto attivo
    Iscritto da
    apr, 2023
    Messaggi:
    1022

    Re: Select Where confronto DatetTime [RISOLTO]

    08/08/2023 - asql ha scritto:

    Ok per la prima … infatti ho visto in diverse documentazioni il consiglio di utilizzare i parametri

    e ok per la seconda, capito bene … ma la devo mettere a fuoco per non incorrere in quanto da te ben spiegato.

    Grazie mille per le precisazioni e consigli molto utili per avere delle buone basi da cui partire.

    ;-)

  • By65Franco
    By65Franco
    Molto attivo
    Iscritto da
    apr, 2023
    Messaggi:
    1022

    Re: Select Where confronto DatetTime [RISOLTO]

    Grazie a tutti perle dritte, consigli e altro…

    dopo un bel po' di Debug sono riuscito a capire e a chiudere anche questa parte dell'App dove praticamente viene popolata una datagridview e sarà possibile applicare filtri per data backup, impostare il numero di righe da visualizzare per pagina, navigare nelle pagine e altri controlli che andrò ad inserire successivamente come per esempio filtrare i files in errore etc etc …

    Utilizzo due void separate; la prima per inizializzare la datagridview in open form oppure al cambio della data selezionata nella combobox
    e la seconda void da richiamare ogni volta che si vuole navigare nella datagridview…. 

            // BACKUP OPERATION DATAGRIDVIEW INIT PAGE
       private void MyInitDatagridviewBkOperation()
       {
           string qrySelect;
           dataTableOperation = new DataTable();
           using (SqlConnection connection = new SqlConnection(ClassMyUtility.cnnString))
           {
               if (dtDateTimeOperation == DateTime.MinValue)
               {
                   dtDateTimeOperation = new DateTime(1900, 1, 1, 12, 0, 0);
                   qrySelect = "SELECT * FROM Tbl_BkOperation WHERE CONVERT(varchar, BkDateTimeBackup, 120) > CONVERT(varchar, @DateTimeOperation, 120)";
               }
               else
               {
                   qrySelect = "SELECT * FROM Tbl_BkOperation WHERE CONVERT(varchar, BkDateTimeBackup, 120) = CONVERT(varchar, @DateTimeOperation, 120)";
               }
               using (SqlCommand command = new SqlCommand(qrySelect, connection))
               { 
                   command.Parameters.AddWithValue("@DateTimeOperation", dtDateTimeOperation);
                   using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                   { 
                       adapter.Fill(dataTableOperation);
                   }
               }
           }
           // load data into datagridview 'myBackup65DataSet.Tbl_BkOperation' with new dataTableOperation
           DataGridBkOperation.DataSource = dataTableOperation;
           // set first page datagridview backup operation
           totalRecords = dataTableOperation.Rows.Count;
           totalPages = (int)Math.Ceiling((double)totalRecords / pageSize);
           pageNumber = 0;
           // load datagridview backup operation
           MyLoadDatagridviewBkOperation();
       }
       
       
       // BACKUP OPERATION DATAGRIDVIEW UPDATE PAGE
       private void MyLoadDatagridviewBkOperation()
       {
           // set buttons 
           BtnNextBkOperation.Enabled = pageNumber < totalPages - 1;
           BtnPreviousBkOperation.Enabled = pageNumber > 0;
           BtnStartBkOperation.Enabled = pageNumber > 0;
           BtnEndBkOperation.Enabled = pageNumber < totalPages - 1;
           // set index start records for page
           startIndex = pageNumber * pageSize;
           // set index end records for page
           endIndex = Math.Min(startIndex + pageSize, totalRecords);
           // set display page of pages
           LblPageOfPage.Text = Convert.ToString(pageNumber + 1) + "/" + Convert.ToString(totalPages);
           LblTotalRows.Text = Convert.ToString(totalRecords);
           // update rows datasource datagridview 
           DataGridBkOperation.DataSource = dataTableOperation.AsEnumerable().Skip(startIndex).Take(pageSize).CopyToDataTable();
           // set focus
           DataGridBkOperation.Focus();
       }

    Un ultima cosa che ho per adesso tamponato con dtDateTimeOperation = new DateTime(1900, 1, 1, 12, 0, 0); (vale quando viene scelto dalla comboBox di visualizzare tutte le date dei backup eseguiti), come mai il dateTime MinValue non viene accettato da
    adapter.Fill(dataTableOperation) …. restituendo l'errore:

    System.Data.SqlTypes.SqlTypeException: ‘Overflow di SqlDateTime. Deve essere compreso tra 1/1/1753 12:00:00 AM e 12/31/9999 11:59:59 PM.’

    Ho visto che viene restituito un valore di dateTime.minValue  =  a  {01/01/0001 00:00:00} e giustamente non l'accetta… ma a questo punto come fare per impostare una data corretta e accettabile per adpter.Fill di dataTable ?  (pertanto come impostare la data più piccola accettabile?)

    Grazie !!!

    P.S. Accetto tutte le critiche, osservazioni  e consigli su come stò scrivendo in c#… grazie ;-)

  • A
    asql
    Molto attivo
    Iscritto da
    dic, 2018
    Messaggi:
    515

    Re: Select Where confronto DatetTime [RISOLTO]

    Salve Fabio.

    come hai visto. C# DateTime.MinValue == 00:00:00.00.000000 UTC, 1 gennaio 0001, nel calendario gregoriano. mentre il SQL DateTime accetta valorizzazioni a partire dal 1753-01-01…

    come hai gia' fatto, utilizza quindi la “prima data” SICURAMENTE non presente in archivio… new DateTime(1900, 1, 1, 0, 0, 0)  (puoi in caso anche omettere la parte ora/minuto/secondo con altro overload) “va concettualmente benissimo” :D

    salutoni romagnoli
    – 
    Andrea

  • By65Franco
    By65Franco
    Molto attivo
    Iscritto da
    apr, 2023
    Messaggi:
    1022

    Re: Select Where confronto DatetTime [RISOLTO]

    08/08/2023 - asql ha scritto:

    Salve Fabio.

    come hai visto. C# DateTime.MinValue == 00:00:00.00.000000 UTC, 1 gennaio 0001, nel calendario gregoriano. mentre il SQL DateTime accetta valorizzazioni a partire dal 1753-01-01…

    come hai gia' fatto, utilizza quindi la “prima data” SICURAMENTE non presente in archivio… new DateTime(1900, 1, 1, 0, 0, 0)  (puoi in caso anche omettere la parte ora/minuto/secondo con altro overload) “va concettualmente benissimo” :D

    salutoni romagnoli
    – 
    Andrea

    Grazie !!!  ok !!!
    salutoni ;-)

  • A
    asql
    Molto attivo
    Iscritto da
    dic, 2018
    Messaggi:
    515

    Re: Select Where confronto DatetTime [RISOLTO]

    Cribio, ho sbagliato…

    Franco, non Fabio… scusa :D

    salutoni romagnoli
    – 
    Andrea

Devi accedere o registrarti per scrivere nel forum
15 risposte