Extract Month Number from a Date

[Stuugie]

Hi all,

I have the following procedure that connects to my database.

Sub getDateModified()

        Dim fileCreatedDate As DateTime = File.GetCreationTime(strFilePath + "\CPI1.csv")
        Dim stmt As String


        SQLConn.ConnectionString = ConnString
        SQLConn.Open()
        stmt = "select Table_Numbers From tblRelease_Dates Where (Release_Date Between '" + fileCreatedDate + "' And GETDATE())"
        SQLCmd.Connection = SQLConn
        SQLCmd.CommandText = stmt
        SQLdr2 = SQLCmd.ExecuteReader

        If SQLdr2.HasRows Then
            While SQLdr2.Read()
                dtRd = SQLdr2.GetSqlInt32(0)
            End While
        End If
        SQLdr2.Close()
        SQLConn.Close()
        SQLConn.ConnectionString = ConnString
        SQLConn.Open()
        stmt = ""
        stmt = "select DATEPART('m',Reference_Period) from tblRelease_Dates where (Release_Date between '" + fileCreatedDate + "' and GETDATE())"
        SQLCmd.CommandText = stmt
        SQLdr2 = SQLCmd.ExecuteReader
        If SQLdr2.HasRows Then
            While SQLdr2.Read()
                eDt = SQLdr2.GetSqlDateTime(0)
            End While
        End If
        SQLConn.Close()
    End Sub

This works up until second instance of:

SQLdr2 = SQLCmd.ExecuteReader

Once it hits this line I get the error: "Invalid parameter 1 specified for datepart" which leads me back to this line:

stmt = "select DATEPART('m',Reference_Period) from tblRelease_Dates where (Release_Date between '" + fileCreatedDate + "' and GETDATE())"

What I am trying to attempt is getting the month number from the date variable called Reference_Period, which is of the type [DT_DBDATE]. I've tried passing this datepart into an integer type and a string type and both ways give me problems so my problem must be the actual DATEPART function.

I really need the month number for the rest of my code to work, can anyone show me the right way?

[Reverend Jim]

Hey Stuggie, how are you enjoying this crappy fall weather?

Try replacing 'm' with just the word, month as in

select DATEPART(month,release_date) from etc

[Stuugie]

Hi RJ, right now the weather at 360 is good. It's when I have to step out for a break that I don't like it ;). I actually wish it would just snow already as cold and rain is a horrible combination. How is it for you with cottage living?

I will replace the m with month and let you know how it goes as soon as I am out of a meeting that starts in a few minutes.

Thanks for your help!

[Stuugie]

Yep, that did the trick. If I could "one-up" you again I would.

[Reverend Jim]

Been home from the cottage since the end of August. Remember that snowstorm a week ago? We were raking leaves in the middle of it. Should have waited (like I wanted to) for the nicer weather that eventually came back.

[Stuugie]

I did my fall cleanup a few days after that snowstorm. This year I refused to rake, I mulched 26 bags worth of leaves and left it all on my grass as well as my flower garden. It took me a while to mulch it all but it was less work and less demanding on some aching parts of my body!

You don't go to your cottage all year round?

[Reverend Jim]

The cottage is a seven hour drive so we go out mid May and usually return mid September. Came back a little early this year for family reasons.