Hello,

I am getting the message Implicit conversion from string to date....
Please help

Protected Sub btnShowDetails_Click(ByVal sender As Object, ByVal e As
EventArgs) Handles btnShowDetails.Click

        Dim startdate As String = txtStartDate.Text
        Dim enddate As String = txtEndDate.Text
        'startdate = Convert.ToDateTime(startdate as date)


        GRCallHistory.DataSource = getcallhistory("PhoneNumber",
"StartDate", "EndDate")
        GRCallHistory.DataBind()

    End Sub

    Function getcallhistory(ByVal Phonenumber As String, _
                            ByVal StartDate As DateTime, _
                            ByVal enddate As DateTime) As
System.Data.SqlClient.SqlDataReader

        Dim Connectionstring As String =
"Server=192.168.225.8;Database=Wiband;uid=Wiband;password=optiplex"
        Dim sqlConnection As System.Data.SqlClient.SqlConnection = New
System.Data.SqlClient.SqlConnection(Connectionstring)
        Dim querystring As String = "select SUBSTRING(CAST(calldate AS
VARCHAR(20)), 1, 11) AS CallDate, SUBSTRING(CAST(calldate AS
VARCHAR(20)), 12, 13) AS CallTime, PhoneNumber, minutes,
calldescription, charge from
voipcalls.dbo.fw_fnVOIPGetCustCallHistory('" & Phonenumber & "', '" &
StartDate & "', '" & enddate & "',  0, -1)"
        Dim sqlCommand As System.Data.SqlClient.SqlCommand = New
System.Data.SqlClient.SqlCommand(querystring, sqlConnection)
        sqlConnection.Open()
        Dim dataReader As System.Data.SqlClient.SqlDataReader =
sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

        Return dataReader
    End Function

Well, what does the string you are trying to cast look like?  You can't
expect people here to take your .NET code, build an application, and make
assumptions about what is actually being passed into values for StartDate
and EndDate.  Heck, a lot of people here don't even have .NET installed.
So, please try to demonstrate what you're trying to convert to a DATETIME
with something a little more direct, e.g.

SELECT CONVERT(DATETIME, 'some string')

A



<liza***@gmail.com> wrote in message
Show quoteHide quote

sorry aaron, this was the first time i ever joined google
group....basically it's taking two arguments from a text box that is
startdate and enddate and pass it to the sql function. this function
has both the parameters as datetime.

Yes, I understand how the program works.  Now show some example input from
the text box.  And do whatyou can to standardize this, e.g. have them pick
from a calendar and build the date for them.  If you allow them free text
entry, they're going to enter all kinds of invalid crap.  What date is
05/06/04?  Is that May 6th 2004, or June 5th 2004, or April 6th 2005?  How
about 05/13/03?  And 7/5/2006?  04/13/13?


<liza***@gmail.com> wrote in message
Show quoteHide quote

aaron, I have people input the date in format 08/01/2005...this is
mandatory

Okay, so show an actual value that fails!




<liza***@gmail.com> wrote in message
Show quoteHide quote

select SUBSTRING(CAST (calldate AS VARCHAR(20)), 1, 11) AS CallDate,
SUBSTRING(CAST(calldate AS VARCHAR(20)), 12, 13) AS CallTime,
PhoneNumber, minutes, calldescription, charge
from voipcalls.dbo.fw_fnVOIPGetCustCallHistory('2549462784',
'08/01/2005', '09/21/2005',  0, -1)

What is fw_fnVOIPGetCustCallHistory ?  Can you give enough DDL and sample
data so that we can at least try to understand WHERE the conversion is
failing???


<liza***@gmail.com> wrote in message
Show quoteHide quote

fw_fnVOIPGetCustCallHistory is a function that returns a table with the
info of phone.

here's the function:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

Function dbo.fw_fnVOIPGetCustCallHistory
(
    @PhoneNumber as VarChar(80),
    @StartDate DateTime,
    @EndDate DateTime,
    @Direction Int,
    @Billable Int
)
RETURNS @CDR TABLE
(
    uniqueid VarChar(32),
    calldate DateTime,
    PhoneNumber VarChar(80),

    CallDescription Varchar(50),
    Direction VarChar(20),
    Minutes Int,
    Charge Money,
    Billable Bit
)
AS
Begin
Declare @CDRWork TABLE
    (
        uniqueid VarChar(32),
        calldate DateTime,
        PhoneNumber VarChar(80),

        L3 VarChar(3),
        S42 VarChar(2),
        S43 VarChar(3),
        S44 VarChar(4),
        CallDescription Varchar(50),
        Direction VarChar(20),
        Minutes Int,
        BillRate Money Default 0,
        Charge Money Default 0,
        BillTypeID Int Default 0, --0=Non-Billable 1=PerMinute 2=PerCall
        Billable Bit,
        PhoneID Int,
        Updated Bit Default 0
    )

    Set @EndDate=DateAdd(d,1,@EndDate)
    If @Direction in (-1,1)
    Begin
        Insert into @CDRWork (uniqueid, PhoneNumber, Minutes, CallDate,
Direction)
        select
            uniqueid, dst as PhoneNumber, Ceiling(Cast(billsec as Decimal)/60)
as Minutes,
            calldate, 'Outgoing' as Direction
        from
            cdr
        Where
            disposition='4' and billsec>0 and
            src=@PhoneNumber and
            calldate>=@StartDate and calldate<@EndDate
    End
    If @Direction in (-1,0)
    Begin
        Insert into @CDRWork (uniqueid, PhoneNumber, Minutes, CallDate,
Direction, Charge, Billable)
        select
            uniqueid, src as PhoneNumber, Ceiling(Cast(billsec as Decimal)/60)
as Minutes,
            calldate, 'Incoming' as Direction,
            0 as Charge, 0 as Billable
        from
            cdr
        Where
            disposition='4' and billsec>0 and
            dst=@PhoneNumber and src<>@PhoneNumber and
            calldate>=@StartDate and calldate<@EndDate
    End
    ----------------------------------------------------------------------
    Update @CDRWork
    Set
        L3 =Left(PhoneNumber,3),
        S42=Substring(PhoneNumber,4,2),
        S43=Substring(PhoneNumber,4,3),
        S44=Substring(PhoneNumber,4,4)
    Where
        Left(PhoneNumber,1)<>'1'
    Update @CDRWork
    Set
        L3 =Substring(PhoneNumber,2,3),
        S42=Substring(PhoneNumber,5,2),
        S43=Substring(PhoneNumber,5,3),
        S44=Substring(PhoneNumber,5,4)
    Where
        Left(PhoneNumber,1)='1'
    ----------------------------------------------------------------------
    /*intl calls using 011 prefix and 2-digit country code*/
    Update a
    Set
        a.Updated=1,
        a.PhoneID=b.PhoneID,
        a.CallDescription=Upper(Rtrim(b.Display)),
        a.BillTypeID=
            Case
                When a.Direction='Outgoing' Then 1
                Else 0
            End,
        a.Billable=
            Case
                When a.Direction='Outgoing' Then b.Billable
                Else 0
            End
    From
        @CDRWork a, fw_PhoneKey b
    where
        a.L3= '011' and
        b.CountryCode=a.S42
    ----------------------------------------------------------------------
    /*intl calls using 011 prefix and 3-digit country code*/
    Update a
    Set
        a.Updated=1,
        a.PhoneID=b.PhoneID,
        a.CallDescription=Upper(Rtrim(b.Display)),
        a.BillTypeID=
            Case
                When a.Direction='Outgoing' Then 1
                Else 0
            End,
        a.Billable=
            Case
                When a.Direction='Outgoing' Then b.Billable
                Else 0
            End
    From
        @CDRWork a, fw_PhoneKey b
    where
        a.Updated=0 and
        a.L3= '011' and
        b.CountryCode=a.S43
    ----------------------------------------------------------------------
    /*intl calls using 011 prefix and 4-digit country code*/
    Update a
    Set
        a.Updated=1,
        a.PhoneID=b.PhoneID,
        a.CallDescription=Upper(Rtrim(b.Display)),
        a.BillTypeID=
            Case
                When a.Direction='Outgoing' Then 1
                Else 0
            End,
        a.Billable=
            Case
                When a.Direction='Outgoing' Then b.Billable
                Else 0
            End
    From
        @CDRWork a, fw_PhoneKey b
    where
        a.Updated=0 and
        a.L3= '011' and
        b.CountryCode=a.S44
    ----------------------------------------------------------------------
    /*normal domestic calls*/
    Update a
    Set
        a.Updated=1,
        a.PhoneID=b.PhoneID,
        a.CallDescription=Upper(Rtrim(b.Display)+' '+b.State),
        a.BillTypeID=
            Case
                When a.Direction='Outgoing' and b.Billable=1 Then 1
                Else 0
            End,
        a.Billable=
            Case
                When a.Direction='Outgoing' Then b.Billable
                Else 0
            End
    From
        @CDRWork a, fw_PhoneKey b
    where
        a.Updated=0 and
        a.L3 = b.npa and
        a.S43 = b.nxx  and
        b.nxx not in ('411', '555', '611')
    ----------------------------------------------------------------------
    /*intl calls like Canada or Caribbean using area codes*/
    Update a
    Set
        a.Updated=1,
        a.PhoneID=b.PhoneID,
        a.CallDescription=Upper(Rtrim(b.Display)),
        a.BillTypeID=
            Case
                When a.Direction='Outgoing' Then 1
                Else 0
            End,
        a.Billable=
            Case
                When a.Direction='Outgoing' Then b.Billable
                Else 0
            End
    From
        @CDRWork a, fw_PhoneKey b
    where
        a.Updated=0 and
        A.L3= b.npa and
        b.billrate > 0 and
        b.nxx is null
    ----------------------------------------------------------------------
    /*information calls (3-digit flavor)*/
    Update a
    Set
        a.Updated=1,
        a.PhoneID=b.PhoneID,
        a.CallDescription=Upper(Rtrim(b.Display)),
        a.BillTypeID=
            Case
                When a.Direction='Outgoing' Then 2
                Else 0
            End,
        a.Billable=
            Case
                When a.Direction='Outgoing' Then b.Billable
                Else 0
            End
    From
        @CDRWork a, fw_PhoneKey b
    where
        a.Updated=0 and
        A.L3= b.nxx and
        b.nxx in ('411','555','611')
    ----------------------------------------------------------------------
    /*information calls (10-digit flavor)*/
    Update a
    Set
        a.Updated=1,
        a.PhoneID=b.PhoneID,
        a.CallDescription=Upper(Rtrim(b.Display)),
        a.BillTypeID=
            Case
                When a.Direction='Outgoing' Then 2
                Else 0
            End,
        a.Billable=
            Case
                When a.Direction='Outgoing' Then b.Billable
                Else 0
            End
    From
        @CDRWork a, fw_PhoneKey b
    where
        a.Updated=0 and
        A.S43= b.nxx and
        b.nxx in ('411','555', '611') and
        a.L3 = b.npa
    ----------------------------------------------------------------------
    /*Calls not locatable in fw_PhoneKey*/
    Update @CDRWork
    Set
        CallDescription='UNKNOWN',
        Charge=0,
        BillTypeID=0,
        Billable=0
    Where
        Updated=0
    ----------------------------------------------------------------------
    If @Billable<>-1
    Begin
        Delete from @CDRWork where Billable<>@Billable
    End
    ----------------------------------------------------------------------
    Update @CDRWork set BillRate=dbo.fw_fnVOIPGetPhoneCallRate(PhoneID,
CallDate)
    where Direction='Outgoing' and Billable=1

    Update @CDRWork set Charge=BillRate Where BillTypeID=2 --Per Call
    Update @CDRWork set Charge=Cast(Minutes as Money) * BillRate Where
BillTypeID=1 --Per Minute
    ----------------------------------------------------------------------
    Insert into @CDR
    (
        uniqueid,
        calldate,
        PhoneNumber,
        CallDescription,
        Minutes,
        Direction,
        Charge,
        Billable
    )
    Select
        uniqueid,
        calldate,
        PhoneNumber,
        CallDescription,
        Minutes,
        Direction,
        Charge,
        Billable
    From @CDRWork
    Order By CallDate Desc

    Return
End

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

When I run this sql query in query analyzer, it runs fine, gives me the
correct results. But when I call it from asp.net webpage, it faild.

On 22 Sep 2005 08:40:25 -0700, liza***@gmail.com wrote:

Hi lizansi,

And what does this mean? The 8th of january? Or August 1st?


On 22 Sep 2005 08:54:02 -0700, liza***@gmail.com wrote:

Ah, from this context, I can see that you expect both the people and
your SQL Server installation to "guess" that your dates are formatted as
mm/dd/yyyy, not dd/mm/yyyy (as the majority of the countries prefer).

I couldn't guess it from 08/01/2005, but I can deduct it from
09/21/2005. Unfortunately, SQL Server is even more lousy at guessing
than I am. I assume that your installation thinks that you speak
dd/mm/yyyy. This causes an error on 09/21/2005, as there is no 21st
month in the Christian calendar.

My advise: have the people enter it in any format you, they, or your
boss prefers. Then have the front-end code do either of these two
things:
1. Convert into native date or datetime format, pass as datetime
parameter to SQL Server, or
2. Reformat to a string in the yyyymmdd format, pass that as string to
SQL Server.

The three only "guaranteed safe" formats for converting string constants
to datetime are:

* yyyymmdd - for date only. (Note: no dashes, slashes, dots, colons, or
other formatting stuff).
* yyyy-mm-ddThh:mm:ss - for date plus time. (Note: dashes in the date
part, colons in the time part, and an uppercase T in the middle).
* yyyy-mm-ddThh:mm:ss.mmm - same as above, but including milliseconds.

Best, Hugo

Hi all thanks a lot for taking your time and trying and replying back
to me:

Finally I got it running....for those who will be interested in knowing
what I did...here it is:

If IsDate(tbxstart) And IsDate(tbxend) Then
            txtStartDate.Text = Format(tbxstart, "dd/mm/yyyy")
            startdate = CDate(tbxstart)

            txtEndDate.Text = Format(tbxend, "dd/mm/yyyy")
            enddate = CDate(tbxend)
end if

Thanks a lot.......Liza.

I hope your SQL server sits behind a DMZ (for your sake)
Anyway the problem is here
dbo.fw_fnVOIPGetCustCallHistory('" & Phonenumber & "', '" &
StartDate & "', '" & enddate & "',  0, -1)"

StartDate  and enddate  are not formatted well

http://sqlservercode.blogspot.com/




Show quoteHide quote

Speeding up queries using TOP
Exporting Stored Procs
LIKE for a Numeric?
Help With Update Please
WHERE clause between two database