[RESOLVED] SQL Server 2008 - CONVERT

[Doogle]

I'm using the following:

ALTER PROCEDURE [dbo].[usp_CountStatusRounds] 
	@TableName sysname,
	@Date varchar(12)	
AS
BEGIN
	SET NOCOUNT ON;
	EXEC('SELECT COUNT(*) FROM ' + 
			@TableName + 
			' WHERE ' +
			'CONVERT (varchar(10), rndDate, 103) = ' + @Date 
	     )
END

Looking at the documentation for the CONVERT Function, the '103' should convert 'rndDate' to 'dd/mm/yyyy' format. However, it's converting to mm/dd/yyyy. Am I missing something ?

[techgnome]

Mind if I ask how you verified this? Also you know that the SQL you're ultimately executing is still incorrect?

-tg

[Doogle]

Mind if I ask how you verified this? Also you know that the SQL you're ultimately executing is still incorrect?

-tg

Thanks for the response. No I didn't realise it's incorrect. (But it doesn't surprise me - I'm a novice with VB2008, SQL Server and Stored Procedures) Perhaps you could point out my error(s).

I 'verified' the observation through trial and error using SQL Server Management Studio to test the Stored Procedure. When I supply '09/06/2012' (dd/mm/yyyy) as the @Date parameter I get a zero result and when I supply '06/09/2012' (mm/dd/yyyy) I'm getting 36 as the result, which is the count I would expect. Likewise with the actual code

    Public Function GetRoundStatusCount(ByVal DepotNumber As Integer) As Integer
        Dim SQLReader As SqlDataReader
        Dim SQLCmd As New SqlCommand
        SQLCmd.Connection = SQLcon
        SQLCmd.CommandType = CommandType.StoredProcedure
        SQLCmd.CommandText = "usp_CountStatusRounds"
        SQLCmd.Parameters.AddWithValue("@TableName", "tblRoundStatus_" + DepotNumber.ToString)
        SQLCmd.Parameters.AddWithValue("@Date", "'" + Format(Now, "MM/dd/yyyy") + "'")
        SQLReader = SQLCmd.ExecuteReader
        Dim NumberOfRounds(0) As Object
        While SQLReader.Read
            SQLReader.GetValues(NumberOfRounds)
        End While
        Dim RoundCount As Integer = NumberOfRounds(0)
        SQLReader.Close()
        SQLCmd.Dispose()
        GetRoundStatusCount = RoundCount
    End Function

The above as shown appears to work whereas if I specify Format(Now,"dd/MM/yyyy") it doesn't (I've already fallen foul of the difference between 'mm' and 'MM')

I'm guessing that CONVERT does work properly and I'm misunderstanding something fairly fundamental.

The Date is set by:

ALTER PROCEDURE [dbo].[usp_UpdateDate]
	@TableName sysname,
	@Date varchar(30)
AS
BEGIN

	SET NOCOUNT ON;

	EXEC('UPDATE '+ @TableName + ' SET rndDate = CONVERT(datetime,' + 
	@Date +', 103) WHERE rndStatus < 0'
		)
END

EDIT: It may help if I describe what I think I'm doing. I have a 'Base' Table containing data for 'rounds' which contains the normal default values. For a new Day, this table is copied into tblRoundStatus_xx and the above procedure is executed to change the default date to today's date. (The default rndStatus column is always less than zero) This should only happen once per day so when the Program starts up it checks if the count of rows for today is zero, if it is, it performs the copy and update, if it's not zero then it doesn't.

I really need to get a grip on Date handling as the Application is going to produce quite a lot of reports based on Date.

As an aside, I'd prefer not to use EXEC and concatinate the SQL in the way I'm doing it, but I haven't found any other method that allows me to pass the Table Name as a parameter. (there's about 35 different tblRoundStatus_xx tables, one for each 'Depot', and I didn't want to write a set of Stored Procedures for each)

[szlamany]

Bad table design - having 35 tables - one for each depot is a poor choice.

Can you still change that design fact?

DEPOT should simply be a field in the "ONE TABLE" you have - and you can then put into your WHERE clause the ability to gave DEPOT=123 or DEPOT=456.

Having 35 tables creates exactly the nightmare you are trying to get around.

Regardless - you are changing a DATE to a VARCHAR() and then comparing it to a variable that is not in single quotes. Your SQL will never work because you are building improper sql strings to execute.

[edit] I see that you are trying to get single quotes around you value when you pass it in from the VB side [/edit]

[Doogle]

Thanks.

Yes, if I had another grey cell I could have thought about having a single Table. As far as the design is concerned, yes I can change it, the whole thing is in the 'Proof of Concept' / 'Prototype' stage.

Still need to understand if there's a 'problem' with my understanding of how to process dates though.

[szlamany]

Dates should be passed in as DATETIME datatypes to the SPROC.

Then the where clause would be:

Where SomeDateField=@DateParam

If you need to make that DYNAMIC SQL then you would build a string something like this:

Declare @ExecStr varchar(100)
Set @ExecStr='Select * From '+@TableName
  +' Where SomeDateField='''+Convert(varchar(10),@DateParam,101)+''''
Print @ExecStr
Exec (@ExecStr)

Note the use of single quotes repeated - so that they appear once in the string.

Also note the PRINT - so you can examine the SQL for proper string building.

[Doogle]

Dates should be passed in as DATETIME datatypes to the SPROC.

Then the where clause would be:

Where SomeDateField=@DateParam

That's where I'm having the problem.
SP:

ALTER PROCEDURE [dbo].[usp_CountStatusRounds] 

	@Date datetime,
	@Depot int
	
AS
BEGIN

	SET NOCOUNT ON;

	SELECT COUNT(*) 
	FROM tblRoundStatus 
	WHERE (rndDate = @Date) AND (rndDepot = @Depot) 
	
END

and the code

        SQLCmd.Parameters.AddWithValue("@Date", Now)

I am still getting a zero count. 'rndDate' is datetime and contains the date and the time. I just want to test for the same date which is why I was using CONVERT. There must be a simple way to test for the date (in dd/mm/yyyy format) only.

[szlamany]

Does RNDDATE contain the TIME???

[Doogle]

Yes - that's the difficulty. I can't see how to just compare the Date part. rndDate is defined as datetime in the Table which is why I was using CONVERT

[techgnome]

the format is irrelevant if the type is datetime... that's why you should use datetime rather than strings. the problem might be your parameter value of "now" which includes the date AND time as of right that instant... 1)I'm guessing that rndDate doesn't include the timestamp, 2) odds are there aren't any records with the exact "now" timestamp, and 3) you probably want to use Date.Today rather than Now.

-tg

[techgnome]

y'all posted while I was still typing.

how to convert & drop the date:

convert(datetime, convert(varchar(10), {put your field or variable here}, 101))

so if you just pass in the date in the parameter (by using today instead of now)
then your where looks like this:

WHERE (convert(datetime, convert(varchar(10), rndDate, 101)) = @Date) AND (rndDepot = @Depot)

-tg

[szlamany]

@doogle - tg gave you the correct answer - but let's make sure you understand something...

The convert w/style 101 is used to "trim the time" as that format only has the DATE represented.

But really important - notice that tg turns it right back into a DATETIME datatype (I would have used Cast(Convert(varchar(10),SomeDateField,101) as datetime) instead). Regardless - try to always treat dates as DATETIME values in the SPROC and you should have no problems. The mm/dd/yyyy vs dd/mm/yyyy is all about DISPLAY FORMATTING.

Your issue all along was trimming the "TIME" - nothing to do with date formatting.

[Doogle]

Well, what can I say except thanks to both of you. It all seems to be working now.