How to calculate expiration date with VB.NET and SQL Server using Visual Studio 2019 and SQL SERVER Enterprise 13?

Question

I'm trying to display on a DataGridView Cell, a value to represent the remaing days between two dates and i'm using DateDiff but it returns an empty cell and i don't know what i'm missing or doing wrong. My Code follows, please help.

Thank you.

Private Sub ValidadeLicencas_Load(sender As Object, e As EventArgs) Handles Me.Load

    Conectar()

    Dim cmd As New SqlCommand("SELECT AlvaraNumero,NomeEmpresa,ContactoEmpresa,ValidadeLicenca FROM EmissaoLicenca", conexoes)

    Dim da As New SqlDataAdapter
    da.SelectCommand = cmd
    Dim dt As New DataTable
    dt.Clear()
    da.Fill(dt)
    DataGridViewValidade.DataSource = dt
    conexoes.Close()

    DataGridViewValidade.AllowUserToAddRows = False

    Dim ColDias As New DataGridViewTextBoxColumn
    ColDias.Name = "drestantes"
    ColDias.HeaderText = "Dias Restantes"
    ColDias.Width = 150
    DataGridViewValidade.Columns.Insert(4, ColDias)

    For Each row As DataGridViewRow In DataGridViewValidade.Rows
        Dim expdata_v As Date = row.Cells("ValidadeLicenca").Value
        Dim calcdias_v As Integer = DateDiff(DateInterval.Year, Date.Now, expdata_v)
        row.Cells("drestantes").Value = calcdias_v
    Next

End Sub

The result should be something like this:

AlvaraNumero	NomeEmpresa	ContactoEmpresa	ValidadeLicenca	Dias Restantes
AlvaraNumero1	NomeEmpresa1	ContactoEmpresa1	30/11/2022	8
AlvaraNumero2	NomeEmpresa2	ContactoEmpresa2	25/11/2022	3

But i get this instead.

AlvaraNumero	NomeEmpresa	ContactoEmpresa	ValidadeLicenca	Dias Restantes
AlvaraNumero1	NomeEmpresa1	ContactoEmpresa1	30/11/2022
AlvaraNumero2	NomeEmpresa2	ContactoEmpresa2	25/11/2022

Answer 1

It would be easier to add the date difference calculation into the SQL query.

SELECT AlvaraNumero,NomeEmpresa,ContactoEmpresa,ValidadeLicenca, DATEDIFF(YEAR, ValidadeLicenca , GETDATE()) [drestantes] FROM EmissaoLicenca