DateDiff year coded improperly?

Transact-SQL
1

I am attempting to get the @Year to read 2017000

Records do exist for this year. However @Year is reporting a response of 120000 (120 + the concat of '000'). What am I overlooking here please?

 DECLARE @reportYear int = DATEDIFF(YYYY, -2, getdate());
 DECLARE @Year int = concat(@reportYear, '000')

SELECT SECT_ID
,DESCRIPT_ID
,LOADNUMBER
FROM tbEXPENSES
WHERE SECT_ID = '31'
AND DESCRIPT_ID = '491'
AND LOADNUMBER = @Year

2

You've got a bug in the first line:

DECLARE @reportYear int = YEAR(getdate()) - 2;

1 Like
3

DOH! Thank you. Always good to have a 2nd set of eyes! LOL

4

USING THE SAME query as above...

Realizing this is not a VB help forum I need to understand how to pass a "No record found." from the SQL to the VB. Note: I am not specifically looking for VB help.

Basically, the IF statement will be changed as required but I need some way to check a specific entry with the loadnumber of yyyy000 ( ex. 2018000) and if it does NOT exist, then send up an alertbox.

Do not get too overly excited over the IF statement as it is something I copied from another sub as a place holder.

I tried using a few various methods having no success. I simply need the SQL to return something (a 1 or 0 possibly, or True/False) that can be used in the IF statement to pop a msgbox if the record does not exist.

Private Sub FHU()
    Dim cmd As New SqlCommand
    Dim reader As SqlDataReader

    cmd.CommandText = ("DECLARE @reportYear int = YEAR(getdate()) - 1
                        DECLARE @Year int = concat(@reportYear, '000')

                        SELECT SECT_ID
                             , DESCRIPT_ID
                             , LOADNUMBER
                          FROM tbEXPENSES
                         WHERE SECT_ID = '31'
                           AND DESCRIPT_ID = '491'
                           AND LOADNUMBER = @Year")

    cmd.CommandType = CommandType.Text
    cmd.Connection = New SqlConnection()
    cmd.Connection.ConnectionString = My.MySettings.Default.---REMOVED---
    cmd.Connection.Open()

    Try
        reader = cmd.ExecuteReader()
        Dim yFHU As Integer

        With reader
            .Read()

  ' yFHU below, will return record number 2018000 if it exists - herein is where the fail occurs as the 
    record does not yet exists.

            yFHU = .GetInt32(2)

   '         If yFHU = 0 Then
   '             Dim frm As New MessageBox2290
   '             frm.ShowDialog()
   '         else
   '            exit
   '         End If

            Return
        End With
        reader.Close()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
    cmd.Connection.Close()
End Sub
5

maybe @@ROWCOUNT?

use sqlteam
go

if exists(select 1 from sys.tables where name = 'tbEXPENSES')
	begin
		drop table tbEXPENSES
	end;

create table tbEXPENSES(SECT_ID varchar(10) , 
DESCRIPT_ID varchar(10) , LOADNUMBER varchar(10))

insert into tbEXPENSES
select 31, 491, '2018000' union
select 32, 491, '2018000' union
select 33, 491, '2018000' union
select 34, 491, '2018000' union
select 35, 491, '2018000' union
select 36, 491, '2018000' 

select * from tbEXPENSES

DECLARE @reportYear int = YEAR(getdate()) - 1
DECLARE @Year int = concat(@reportYear, '000')

SELECT SECT_ID
        , DESCRIPT_ID
        , LOADNUMBER
    FROM tbEXPENSES
    WHERE SECT_ID = '31'
    AND DESCRIPT_ID = '491'
    AND LOADNUMBER = @Year

select @@ROWCOUNT


SELECT SECT_ID
        , DESCRIPT_ID
        , LOADNUMBER
    FROM tbEXPENSES
    WHERE DESCRIPT_ID = '44591'
    
IF @@ROWCOUNT = 0  
	PRINT 'No record found.';
6

I'm not trying to insert anything into the table until it's proper time.

Re: ROWCOUNT: I tried that but need to pass a 1 or a 0 or conversely, true/false into the VB. This is where I am struggling. Not sure how to take a "not found" response and convert it to a usable response in the VB part of the code.

7

The insert is emulating the sample data you never provided. Otherwise it will just be a guess. You need to use a proper stored procedure?

8

Yeah, I figured that out after I posted that - sorry...

So I reworked things a bit and and am using the following:

Private Sub FHUYear()
    Dim FHUYear As Integer

    Dim sqlConn As New SqlConnection
    Try
        Using sqlConn
            sqlConn.ConnectionString = My.MySettings.Default.PETS_DatabaseConnectionString
            sqlConn.Open()
            Using sqlCmd As New SqlCommand
                sqlCmd.CommandText = "DECLARE @reportYear int = YEAR(getdate()) - 2
                            DECLARE @Year int = concat(@reportYear, '000')

                            SELECT Count(*) FROM tbEXPENSES WHERE LOADNUMBER = @Year"

                sqlCmd.Parameters.AddWithValue("@FHUYear", SqlDbType.Int).Value = FHUYear
            End Using
        End Using

        Select Case FHUYear
            Case Is = 0
                Dim frm As New MessageBox2290
                frm.ShowDialog()
            Case Is >= 1
                Exit Sub
        End Select

    Catch ex As Exception
        MessageBox.Show(ex.Message)
        'Return False
    Finally
        sqlConn.Close()
    End Try
End Sub

Problem is I am always returning a '0' even if a record exists?

FYI: The DECLARE is using 2 years from current only to ensure I return proper result of '1' since last year's (2018) has not yet been entered. However this is returning a '0' as well.

In the MSSMS the result returned is a '1' which is what I expected to see. Yet using the exact same query inside the VB.NET form it is always returning a '0.'

I'm pretty sure it is an error with the AddWithValue parameter in which I do not fully understand how to implement this.

9

Well ..it always will as you are only checking .FHUYear not the existence of records or not

10

OK, got the issue resolved. I just created a temp db and did a record count. That worked. Thanks for the assist.

1 Like
11

hi

i tried to do this
please see my attempt

I am using dateadd instead of datediff

SQL 
DECLARE @reportYear  int =  year(DateAdd(yyyy, -2, GetDate()))
DECLARE @Year int = concat(@reportYear, '000')
select @Year

image