Converting US Date To UK Date in Reporting Services

I am using thise function in the Code Properties of my Report to convert US Date formats to UK Date format :

Public Function ConvertUSDateToUKDate(ByVal strUSDate As Object) As String
Dim strDay As String = strUSDate.Substring(3, 2)
Dim strMonth As String = strUSDate.Substring(0, 2)
Dim strYear As String = strUSDate.Substring(6, 4)
Dim strUKDate As String = strDay + "/" + strMonth + "/" + strYear + strUSDate.Substring(10, 12)
Return strUKDate
End Function

I keep getting an error with it, I'm not sure whether it is a VB.NET code error, or a Reporting Services error. Can anybody help?

Cheers,

Mike

Sorted this out :

Public Function ConvertUSDateToUKDate(ByVal strUSDate As String) As String
Dim strUKDate As String = ""
If Not (strUSDate = "") Then
Dim arrRateArray As String()
Dim arrSeparator As Char() = {"/"C}
arrRateArray = strUSDate.Split(arrSeparator)
Dim strMonth As String = arrRateArray(0)
Dim strDay As String = arrRateArray(1)
Dim strYear As String = arrRateArray(2).Substring(0, 4)
Dim strTime As String = arrRateArray(2).Substring(5, arrRateArray(2).Length - 5)
strUKDate = strDay + "/" + strMonth + "/" + strYear + " " + strTime
Else
strUKDate = ""
End If

converting to date only

I have a function on a MSSQL 2000 db like the following:

create function GetDateOnly (@.pInputDate datetime)
returns datetime
as
begin
return cast(convert(varchar(10), @.pInputDate, 111) as datetime)
end

which returns the date with the time all zeros ( '2006-05-09 00:00:00' ). I tried to implement this same function on an MSSQL 7 server and I get errors.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'function'.

How can I code something similar in version 7? I'm assuming it's a version difference that is causing my problems.

Thanks,
Randy7.0 did not support user-defined functions.

You could write a stored proc with an OUTPUT parameter to do this, but you could not include it in a SELECT statement as you can with a function.

You will probably need to code the logic each time you need it. But I suggest you use this formula instead, which is more efficient than the CONVERT method you are using:

create function GetDateOnly (@.pInputDate datetime)
returns datetime
as
begin
return dateadd(day, datediff(day, 0, getdate()), 0)
end|||thanks alot for the advise. I thought about stored procedure, but then like you mentioned I can't use it in a SELECT which was my whole goal. Back to the drawing board :-)

Randy

Converting string to int

Hi,

This probably is a basic question but I can't figure it out...

Because SQL Server's ISNUMERIC function allows some strange values to count as numeric (such as '\'), I want to create my own function that will only return an integer if the value is able to be converted (otherwise it will return 0). I have created the following function to do this:

CREATE FUNCTION [dbo].[fnConvertToInt]
(
@.str nvarchar(30)
)
RETURNS int
AS
BEGIN
DECLARE @.s int

BEGIN TRY
SET @.s = convert(int, @.str)
END TRY
BEGIN CATCH
SET @.s = 0
END CATCH
-- Return the result of the function
RETURN @.s
END

When I run this however, I get errors from having the TRY CATCH in a function:

Invalid use of side-effecting or time-dependent operator in 'BEGIN TRY' within a function.

How can I convert a string to an integer without getting an error? I am using SQL Server 2005.

Hi,

the isnumeric function is hazle, but you can implement some isreallynumeric, like aaron did this on his website:

http://www.aspfaq.com/show.asp?id=2390

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Converting Oracle Encode Function into T-SQL syntax

I am looking for the correct syntax to convert Oracle encode function into T-SQl syntax. Please provide specific syntax and examples.
Thank you.Don't you mean 'decode' function?|||did you see my reply on this thread (http://dbforums.com/showthread.php?s=&threadid=444782) ?

rudy