Date problem with ASP.NET but not in VB.NET Winform App

Edited by SomeNewKid. Please post code between<code> and</code> tags.

---

Hello, I have given up after 3 hours of trying to get a DBNull.Value to be inserted into SQL 2000 DB. Below is the code that exists in an object that I use both from a VB.NET Windows application and from an ASP.NET application. (same exact compiled object dll). Has ran fine with windows application for almost a year - still does. Use the same object in my new ASP.NET application and I get a SQL Overflow error that says date must be between 1753 12 am etc etc... when trying to insert null using the code below.

This one has me stumped. The lines below where I set the date are basically this (what the logic equates to):

oDR.Item("CT_CustomDate1") = DBNull.Value
oDR.Item("CT_CustomDate2") = DBNull.Value

Error gets thrown when this line is executed:

oDA.Update(oDT)

Also, the CT_CustomDate1 & 2 fields in the SQL Server 2000 DB are of type SmallDateTime and the variables in object used in code function below are Date. I've tried every combination of Date, DateTime, SmallDateTime, etc. etc. to no avail.

Thanks for you help!

 Public Function AddNew(ByVal sGUID As String) As Boolean
 'Saves all new contact information to database
 Dim sSQL As String
 sSQL = "SELECT * FROM Contact WHERE 1=2"
 Dim oConn As New SqlConnection(sConnectionString)
 Dim oDT As New DataTable
 Dim oDA As New SqlDataAdapter(sSQL, oConn)
 Dim oDR As DataRow
 Dim bSuccess As Boolean
 Try
 oDA.Fill(oDT)
 Dim dataCommandBuilder As New SqlCommandBuilder(oDA)
 oDA.InsertCommand = dataCommandBuilder.GetInsertCommand
 oDA.DeleteCommand = dataCommandBuilder.GetDeleteCommand
 oDA.UpdateCommand = dataCommandBuilder.GetUpdateCommand
 oDR = oDT.NewRow
 oDR.Item("CT_GUID") = sGUID
 oDR.Item("CT_CompanyGUID") = CTCompanyGUID
 oDR.Item("CT_Prefix") = CTPrefix
 oDR.Item("CT_FirstName") = CTFirstName
 oDR.Item("CT_MiddleName") = CTMiddleName
 oDR.Item("CT_LastName") = CTLastName
 oDR.Item("CT_Suffix") = CTSuffix
 oDR.Item("CT_Title") = CTTitle
 oDR.Item("CT_ContactAddress") = CTContactAddress
 oDR.Item("CT_Address1") = CTAddress1
 oDR.Item("CT_Address2") = CTAddress2
 oDR.Item("CT_City") = CTCity
 oDR.Item("CT_State") = CTState
 oDR.Item("CT_Zip") = CTZip
 oDR.Item("CT_Country") = CTCountry
 oDR.Item("CT_InternationalPhone") = CTInternationalPhone
 oDR.Item("CT_Phone") = CTPhone
 oDR.Item("CT_Fax") = CTFax
 oDR.Item("CT_Cell") = CTCell
 oDR.Item("CT_AltPhone") = CTAltPhone
 oDR.Item("CT_Assistant") = CTAssistant
 oDR.Item("CT_AssistantPhone") = CTAssistantPhone
 oDR.Item("CT_NoEmail") = CTNoEmail
 oDR.Item("CT_EmailAddress") = CTEmailAddress
 oDR.Item("CT_Confidential") = CTConfidential
 oDR.Item("CT_TypeGUID") = CTTypeGUID
 oDR.Item("CT_Designation") = ""
 oDR.Item("CT_LastUpdate") = CTLastUpdate
 oDR.Item("CT_UpdatedByWho") = CTUpdatedByWho
 oDR.Item("CT_Location") = CTLocation
 oDR.Item("CT_CustomBit1") = CTCustomBit1
 oDR.Item("CT_CustomBit2") = CTCustomBit2
 oDR.Item("CT_CustomBit3") = CTCustomBit3
 oDR.Item("CT_CustomBit4") = CTCustomBit4
 oDR.Item("CT_CustomBit5") = CTCustomBit5
 oDR.Item("CT_CustomBit6") = CTCustomBit6
 oDR.Item("CT_CustomBit7") = CTCustomBit7
 oDR.Item("CT_CustomStr1") = CTCustomStr1
 oDR.Item("CT_CustomStr2") = CTCustomStr2
 oDR.Item("CT_CustomStr3") = CTCustomStr3
 oDR.Item("CT_CustomStr4") = CTCustomStr4
 oDR.Item("CT_CustomStr5") = CTCustomStr5
 oDR.Item("CT_CustomStr6") = CTCustomStr6
 oDR.Item("CT_CustomStr7") = CTCustomStr7
 oDR.Item("CT_CustomStr8") = CTCustomStr8
 oDR.Item("CT_CustomStr9") = CTCustomStr9
 oDR.Item("CT_CustomStr10") = CTCustomStr10
 oDR.Item("CT_CustomStr11") = CTCustomStr11
 oDR.Item("CT_CustomStr12") = CTCustomStr12
 oDR.Item("CT_CustomStr13") = CTCustomStr13
 oDR.Item("CT_CustomPhone1") = CTCustomPhone1
 oDR.Item("CT_CustomPhone2") = CTCustomPhone2
 oDR.Item("CT_CustomPhone3") = CTCustomPhone3
 oDR.Item("CT_CustomPhone4") = CTCustomPhone4
 oDR.Item("CT_CustomDate1") = IIf(CTCustomDate1 = #12:00:00 AM#, DBNull.Value, CTCustomDate1)
 oDR.Item("CT_CustomDate2") = IIf(CTCustomDate2 = #12:00:00 AM#, DBNull.Value, CTCustomDate2)
 'oDR.Item("CT_CustomDate1") = CDate(#1/1/1753#)
 'oDR.Item("CT_CustomDate2") = CDate(#1/1/1753#)
 oDR.Item("CT_CustomAmount1") = CTCustomAmount1
 oDR.Item("CT_CustomAmount2") = CTCustomAmount2
 oDR.Item("CT_CustomType1GUID") = CTCustomType1GUID
 oDR.Item("CT_CustomType2GUID") = CTCustomType2GUID
 oDR.Item("CT_CustomCompany1GUID") = IIf(CTCustomCompany1GUID = "00", DBNull.Value, CTCustomCompany1GUID)
 oDR.Item("CT_CustomCompany2GUID") = IIf(CTCustomCompany2GUID = "00", DBNull.Value, CTCustomCompany2GUID)
 oDR.Item("CT_CustomContact1GUID") = IIf(CTCustomContact1GUID = "00", DBNull.Value, CTCustomContact1GUID)
 oDT.Rows.Add(oDR)
 oDA.Update(oDT)
 CTGUID = sGUID
 bSuccess = True
 Catch err As Exception
 MsgBox("Error saving new contact..." & vbCrLf & vbCrLf & err.Message, MsgBoxStyle.Critical + MsgBoxStyle.OKOnly, "Database Error")
 bSuccess = False
 End Try
 AddNew = bSuccess
 End Function

Are you sure your ASP.NET application are referencing the same assemblies as the
WinForm?
Are they running under the same Framework version?
DataAccessApplicationBlock?

Regards
Fredriksql

Date problem

Hi,
This code below in VB.net , delete a record in the database next day from inserting it to DB, for example:
If I add new record today 14-7-2005, and tommorow 15-7-2005 open to view the records available, i will not find it this record because it was deleted.

PrivateSub checkdate()

Dim ssqlAsString

Dim start_dateAsDate

Dim updcmdAs SqlClient.SqlCommand

mysqladap =New SqlClient.SqlDataAdapter(" select start_date from auction ", mySqlConn)

If Today.Date > start_dateThen

ssql = "delete auction where start_date < '" & Today.Date & "'"

updcmd =New SqlClient.SqlCommand(ssql, mySqlConn)

updcmd.ExecuteNonQuery()

EndIf

EndSub

BUTI want to change that code in a way that the record will be deleted after 3 days from the date it was inserted, that means the period for each record to stay in the DB is 3 days and after that will be deleted , is that possible ?? if yes how can it be changed??
Regards

I think you ought to insert a timestamp to record the datetime it was inserted (eg TSINSERT).
After this, you can delete all the records older than three days:
"DELETE FROM TABLE WHERE TSINSERT < " & Now.AddDays(-3).ToString
Another thing which you might do is to schedule a job on the database to automatically do the cleaning for you.
(but i have no experience in that so far)

Date Prob

In Sa the date format is dd/mm/yy. in sql server the format the format
is mm/dd/yy.
What code can be used to chnge the format to the SA date format
eithout chnging sql servers regional settings?Input or output?
This is a bigger question than what it might seem, so I suggest you start by reading up on the
basics, and you can then qualify the question: http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"amatuer" <njoosub@.gmail.com> wrote in message
news:1147097192.284336.19140@.y43g2000cwc.googlegroups.com...
> In Sa the date format is dd/mm/yy. in sql server the format the format
> is mm/dd/yy.
> What code can be used to chnge the format to the SA date format
> eithout chnging sql servers regional settings?
>|||Does SET DATEFORMAT give you what you want?
--
Keith Kratochvil
"amatuer" <njoosub@.gmail.com> wrote in message
news:1147097192.284336.19140@.y43g2000cwc.googlegroups.com...
> In Sa the date format is dd/mm/yy. in sql server the format the format
> is mm/dd/yy.
> What code can be used to chnge the format to the SA date format
> eithout chnging sql servers regional settings?
>

Date Parsing using T-SQL

I can do this using MS Access 2000 database code.
String called
DKEY: "199306 30"
Using MS Access to convert that to a date the following code works:
DateValue(Mid(Replace([dkey]," ",""),5,2) & "/" & Mid(Replace([dkey],"
",""),7,3) & "/" & Mid(Replace([dkey]," ",""),1,4))
Returns the date value: 6/30/1993
How is this done using T-SQL? Any help greatly appreciated!!!
RBollingerIf your datestring is always formatted in that fashion (ie,
"YYYYMM[spaces]dd") , then the easiest thing to do is to remove the
spaces and convert it to a date.
SELECT CONVERT(smalldatetime, REPLACE(DKEY, ' ', ''))
HTH,
Stu
robboll wrote:
> I can do this using MS Access 2000 database code.
> String called
> DKEY: "199306 30"
> Using MS Access to convert that to a date the following code works:
> DateValue(Mid(Replace([dkey]," ",""),5,2) & "/" & Mid(Replace([dkey],"
> ",""),7,3) & "/" & Mid(Replace([dkey]," ",""),1,4))
> Returns the date value: 6/30/1993
> How is this done using T-SQL? Any help greatly appreciated!!!
> RBollinger|||Convert(datetime, Replace([dkey], ' ',''), 112)
Tom
"robboll" <robboll@.hotmail.com> wrote in message
news:1149630420.422200.35050@.u72g2000cwu.googlegroups.com...
>I can do this using MS Access 2000 database code.
> String called
> DKEY: "199306 30"
> Using MS Access to convert that to a date the following code works:
> DateValue(Mid(Replace([dkey]," ",""),5,2) & "/" & Mid(Replace([dkey],"
> ",""),7,3) & "/" & Mid(Replace([dkey]," ",""),1,4))
> Returns the date value: 6/30/1993
> How is this done using T-SQL? Any help greatly appreciated!!!
> RBollinger
>|||thanks!
Tom Cooper wrote:
> Convert(datetime, Replace([dkey], ' ',''), 112)
> Tom
> "robboll" <robboll@.hotmail.com> wrote in message
> news:1149630420.422200.35050@.u72g2000cwu.googlegroups.com...|||I get an error when you trying this. In Access you have to accout for
a - or / or . delimiter between dates. Does the same apply to T-SQL?
SELECT CONVERT(smalldatetime, REPLACE(DKEY, ' ', ''))
Stu wrote:
> If your datestring is always formatted in that fashion (ie,
> "YYYYMM[spaces]dd") , then the easiest thing to do is to remove the
> spaces and convert it to a date.
> SELECT CONVERT(smalldatetime, REPLACE(DKEY, ' ', ''))
> HTH,
> Stu
>
> robboll wrote:|||I get an error when trying this. In Access you have to accout for a -
or / or . delimiter between dates. Does the same apply to T-SQL?
Tom Cooper wrote:
> Convert(datetime, Replace([dkey], ' ',''), 112)
> Tom
> "robboll" <robboll@.hotmail.com> wrote in message
> news:1149630420.422200.35050@.u72g2000cwu.googlegroups.com...|||SUBSTRING(dkey, 9, 3) + '/' + SUBSTRING(dkey, 5, 2) + '/' +
SUBSTRING(dkey, 1, 4)
provides a readable format, but there are no leading zeros in the day
like there are in the month, and it's still a text string.
convert(smalldatetime, SUBSTRING(dkey, 9, 3) + '/' + SUBSTRING(dkey, 5,
2) + '/' + SUBSTRING(dkey, 1, 4)) doesn't seem to work
robboll wrote:
> I get an error when trying this. In Access you have to accout for a -
> or / or . delimiter between dates. Does the same apply to T-SQL?
> Tom Cooper wrote:|||Okay -- This works. Y'all got me on the right track. Thank you!
CONVERT (smalldatetime, SUBSTRING(dkey, 5, 2) + '/' +
LTRIM(SUBSTRING(dkey, 9, 3)) + '/' + SUBSTRING(dkey, 1, 4))
robboll wrote:
> SUBSTRING(dkey, 9, 3) + '/' + SUBSTRING(dkey, 5, 2) + '/' +
> SUBSTRING(dkey, 1, 4)
> provides a readable format, but there are no leading zeros in the day
> like there are in the month, and it's still a text string.
> convert(smalldatetime, SUBSTRING(dkey, 9, 3) + '/' + SUBSTRING(dkey, 5,
> 2) + '/' + SUBSTRING(dkey, 1, 4)) doesn't seem to work
>
> robboll wrote:|||What error did you get? In SQL, a format of 20060601 is preferred;
it's unambiguous, and the pattern you provided should have matched
that. The only thing I can think of is that there must be some other
delimiters besides spaces.
I see you found a solution, but was just curious.
robboll wrote:
> I get an error when you trying this. In Access you have to accout for
> a - or / or . delimiter between dates. Does the same apply to T-SQL?
> SELECT CONVERT(smalldatetime, REPLACE(DKEY, ' ', ''))
> Stu wrote:|||For some reason I think the problem had to do with an inconsistent
string value where the month had leading zeros and the days didn't. To
get around this I used an acceptable date delimiter "/". But you're
absolutely correct it will work with the string date in your example.
Unfortunately mine was 2006061.
Stu wrote:
> What error did you get? In SQL, a format of 20060601 is preferred;
> it's unambiguous, and the pattern you provided should have matched
> that. The only thing I can think of is that there must be some other
> delimiters besides spaces.
> I see you found a solution, but was just curious.
> robboll wrote:

Date Parameter with ReportViewer

Hi All,

I have the following code which works using the ReportViewer component. However how do I pass in the current date i.e Now?

When I use DateTime.Today.ToString I get the following error: The value provided for the report parameter 'SnapshotDate' is not valid for its type. SnapshoteDate is defined as DateTime.

If Not IsPostBack Then
ReportViewer1.ServerUrl = "http://devnet2/ReportServer"
ReportViewer1.ReportPath = "%2fCorVuReports/LAA/Pie&SnapshotDate=06/01/2004"
ReportViewer1.Toolbar = Microsoft.Samples.ReportingServices.ReportViewer.multiState.False
ReportViewer1.Zoom = "75"

End If

I managed to solve this...

cheers

|||

Could you please post your resolution, so others (like us) who encounter the problem could benefit from your wisdom.

Much appreciated.

Date Parameter

Have a report that requires a @.StartDate parameter. This will equal a
ActualDateTime fields in a table.
I have the following code listed in my where clause
and (tvo.ActualDateTime = @.StartDate)
but keeps getting throwing an error when i test. As we are in New Zealand
our date format is dd/MM/yyyy but when entering a start date in this format i
get an "arithmetic overflow error converting expression to date type
smalldatetime". I assume this is because the database is storing the field as
a datetime and its format is MM/dd/yyyy. I have set the parameter to
datatype datetime. I know this is probably easy to sort, just need a little
assistance.
Cheers.Are you getting this error AFTER you set the parameter to datetime?
It is understandable when the parameter is string, you'd have to format it
correctly before sending it to SQL..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
news:9A05E7D1-8DA9-44DB-B5E7-5271DEDC3425@.microsoft.com...
> Have a report that requires a @.StartDate parameter. This will equal a
> ActualDateTime fields in a table.
> I have the following code listed in my where clause
> and (tvo.ActualDateTime = @.StartDate)
> but keeps getting throwing an error when i test. As we are in New Zealand
> our date format is dd/MM/yyyy but when entering a start date in this
> format i
> get an "arithmetic overflow error converting expression to date type
> smalldatetime". I assume this is because the database is storing the field
> as
> a datetime and its format is MM/dd/yyyy. I have set the parameter to
> datatype datetime. I know this is probably easy to sort, just need a
> little
> assistance.
> Cheers.|||Cheers Wayne
I have run the report in the preview tab without the parameter statement in
the where clause and i get data returned.
The datatype of the datetime field that I need the @.StartDate parameter to
match is of smalldatetime type.
With the @.StartDate parameter set to datetime I get data returned, no
problem there. But only if i enter the date into the parameter box as
MM/dd/yyyy. I want to be able to enter it as dd/MM/yyyy and have it display
the correct data.
hope this makes it a bit clearer.
i assume i have to convert the date time just not sure on the syntax required
"Wayne Snyder" wrote:
> Are you getting this error AFTER you set the parameter to datetime?
> It is understandable when the parameter is string, you'd have to format it
> correctly before sending it to SQL..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
> news:9A05E7D1-8DA9-44DB-B5E7-5271DEDC3425@.microsoft.com...
> > Have a report that requires a @.StartDate parameter. This will equal a
> > ActualDateTime fields in a table.
> >
> > I have the following code listed in my where clause
> >
> > and (tvo.ActualDateTime = @.StartDate)
> >
> > but keeps getting throwing an error when i test. As we are in New Zealand
> > our date format is dd/MM/yyyy but when entering a start date in this
> > format i
> > get an "arithmetic overflow error converting expression to date type
> > smalldatetime". I assume this is because the database is storing the field
> > as
> > a datetime and its format is MM/dd/yyyy. I have set the parameter to
> > datatype datetime. I know this is probably easy to sort, just need a
> > little
> > assistance.
> >
> > Cheers.
>
>|||Check the code of your report. The second to last line in your XML will be:
<Language>en-US</Language>
Change it to:
<Language>en-NZ</Language>
Also ensure you have SP1 at least installed.
"Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
news:F5DCEDDB-4819-4C5F-BC8B-44A4CFA80163@.microsoft.com...
> Cheers Wayne
> I have run the report in the preview tab without the parameter statement
in
> the where clause and i get data returned.
> The datatype of the datetime field that I need the @.StartDate parameter to
> match is of smalldatetime type.
> With the @.StartDate parameter set to datetime I get data returned, no
> problem there. But only if i enter the date into the parameter box as
> MM/dd/yyyy. I want to be able to enter it as dd/MM/yyyy and have it
display
> the correct data.
> hope this makes it a bit clearer.
> i assume i have to convert the date time just not sure on the syntax
required
>
> "Wayne Snyder" wrote:
> > Are you getting this error AFTER you set the parameter to datetime?
> >
> > It is understandable when the parameter is string, you'd have to format
it
> > correctly before sending it to SQL..
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
> > news:9A05E7D1-8DA9-44DB-B5E7-5271DEDC3425@.microsoft.com...
> > > Have a report that requires a @.StartDate parameter. This will equal a
> > > ActualDateTime fields in a table.
> > >
> > > I have the following code listed in my where clause
> > >
> > > and (tvo.ActualDateTime = @.StartDate)
> > >
> > > but keeps getting throwing an error when i test. As we are in New
Zealand
> > > our date format is dd/MM/yyyy but when entering a start date in this
> > > format i
> > > get an "arithmetic overflow error converting expression to date type
> > > smalldatetime". I assume this is because the database is storing the
field
> > > as
> > > a datetime and its format is MM/dd/yyyy. I have set the parameter to
> > > datatype datetime. I know this is probably easy to sort, just need a
> > > little
> > > assistance.
> > >
> > > Cheers.
> >
> >
> >|||Nat,
If your parameter is set to datetime, then there is a bug in the
preview tab that doesn't translate to dd/mm/yyyy it assumes US format.
I found the solution to be in the preview tab use yyyy-mm-dd, it seems
to be a universal format for SQL. DateTime is not 'stored' in any
national format, it's just a number which gets formatted based on
locale.
You'll probably find it works OK when deployed!
Chris
AshVsAOD wrote:
> Check the code of your report. The second to last line in your XML
> will be: <Language>en-US</Language>
> Change it to:
> <Language>en-NZ</Language>
>
> Also ensure you have SP1 at least installed.
> "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
> news:F5DCEDDB-4819-4C5F-BC8B-44A4CFA80163@.microsoft.com...
> > Cheers Wayne
> >
> > I have run the report in the preview tab without the parameter
> > statement
> in
> > the where clause and i get data returned.
> >
> > The datatype of the datetime field that I need the @.StartDate
> > parameter to match is of smalldatetime type.
> >
> > With the @.StartDate parameter set to datetime I get data returned,
> > no problem there. But only if i enter the date into the parameter
> > box as MM/dd/yyyy. I want to be able to enter it as dd/MM/yyyy and
> > have it
> display
> > the correct data.
> >
> > hope this makes it a bit clearer.
> >
> > i assume i have to convert the date time just not sure on the syntax
> required
> >
> >
> >
> > "Wayne Snyder" wrote:
> >
> > > Are you getting this error AFTER you set the parameter to
> > > datetime?
> > >
> > > It is understandable when the parameter is string, you'd have to
> > > format
> it
> > > correctly before sending it to SQL..
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Mariner, Charlotte, NC
> > > www.mariner-usa.com
> > > (Please respond only to the newsgroups.)
> > >
> > > I support the Professional Association of SQL Server (PASS) and
> > > it's community of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > > "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in
> > > message news:9A05E7D1-8DA9-44DB-B5E7-5271DEDC3425@.microsoft.com...
> > > > Have a report that requires a @.StartDate parameter. This will
> > > > equal a ActualDateTime fields in a table.
> > > >
> > > > I have the following code listed in my where clause
> > > >
> > > > and (tvo.ActualDateTime = @.StartDate)
> > > >
> > > > but keeps getting throwing an error when i test. As we are in
> > > > New
> Zealand
> > > > our date format is dd/MM/yyyy but when entering a start date in
> > > > this format i
> > > > get an "arithmetic overflow error converting expression to date
> > > > type smalldatetime". I assume this is because the database is
> > > > storing the
> field
> > > > as
> > > > a datetime and its format is MM/dd/yyyy. I have set the
> > > > parameter to datatype datetime. I know this is probably easy to
> > > > sort, just need a little
> > > > assistance.
> > > >
> > > > Cheers.
> > >
> > >
> > >|||Thanks Chris
and you were right...works fine once deployed. just testing at preview
doesn't show correct date format...oh well at least it works...just wish i
hadn't spent so much time trying to fix something i couldn't.
have a good day...
"Chris McGuigan" wrote:
> Nat,
> If your parameter is set to datetime, then there is a bug in the
> preview tab that doesn't translate to dd/mm/yyyy it assumes US format.
> I found the solution to be in the preview tab use yyyy-mm-dd, it seems
> to be a universal format for SQL. DateTime is not 'stored' in any
> national format, it's just a number which gets formatted based on
> locale.
> You'll probably find it works OK when deployed!
> Chris
>
> AshVsAOD wrote:
> > Check the code of your report. The second to last line in your XML
> > will be: <Language>en-US</Language>
> >
> > Change it to:
> >
> > <Language>en-NZ</Language>
> >
> >
> >
> > Also ensure you have SP1 at least installed.
> >
> > "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
> > news:F5DCEDDB-4819-4C5F-BC8B-44A4CFA80163@.microsoft.com...
> > > Cheers Wayne
> > >
> > > I have run the report in the preview tab without the parameter
> > > statement
> > in
> > > the where clause and i get data returned.
> > >
> > > The datatype of the datetime field that I need the @.StartDate
> > > parameter to match is of smalldatetime type.
> > >
> > > With the @.StartDate parameter set to datetime I get data returned,
> > > no problem there. But only if i enter the date into the parameter
> > > box as MM/dd/yyyy. I want to be able to enter it as dd/MM/yyyy and
> > > have it
> > display
> > > the correct data.
> > >
> > > hope this makes it a bit clearer.
> > >
> > > i assume i have to convert the date time just not sure on the syntax
> > required
> > >
> > >
> > >
> > > "Wayne Snyder" wrote:
> > >
> > > > Are you getting this error AFTER you set the parameter to
> > > > datetime?
> > > >
> > > > It is understandable when the parameter is string, you'd have to
> > > > format
> > it
> > > > correctly before sending it to SQL..
> > > >
> > > > --
> > > > Wayne Snyder, MCDBA, SQL Server MVP
> > > > Mariner, Charlotte, NC
> > > > www.mariner-usa.com
> > > > (Please respond only to the newsgroups.)
> > > >
> > > > I support the Professional Association of SQL Server (PASS) and
> > > > it's community of SQL Server professionals.
> > > > www.sqlpass.org
> > > >
> > > > "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in
> > > > message news:9A05E7D1-8DA9-44DB-B5E7-5271DEDC3425@.microsoft.com...
> > > > > Have a report that requires a @.StartDate parameter. This will
> > > > > equal a ActualDateTime fields in a table.
> > > > >
> > > > > I have the following code listed in my where clause
> > > > >
> > > > > and (tvo.ActualDateTime = @.StartDate)
> > > > >
> > > > > but keeps getting throwing an error when i test. As we are in
> > > > > New
> > Zealand
> > > > > our date format is dd/MM/yyyy but when entering a start date in
> > > > > this format i
> > > > > get an "arithmetic overflow error converting expression to date
> > > > > type smalldatetime". I assume this is because the database is
> > > > > storing the
> > field
> > > > > as
> > > > > a datetime and its format is MM/dd/yyyy. I have set the
> > > > > parameter to datatype datetime. I know this is probably easy to
> > > > > sort, just need a little
> > > > > assistance.
> > > > >
> > > > > Cheers.
> > > >
> > > >
> > > >
>|||I know what you mean! I found this out the hard way too!
If something doesn't seem right in preview, it's often worth deploying
and seeing if it's OK there. The rendering engine in Preview is not the
same as in Report Manager.
Chris
Nat Johnson wrote:
> Thanks Chris
> and you were right...works fine once deployed. just testing at
> preview doesn't show correct date format...oh well at least it
> works...just wish i hadn't spent so much time trying to fix
> something i couldn't.
> have a good day...
> "Chris McGuigan" wrote:
> > Nat,
> > If your parameter is set to datetime, then there is a bug in the
> > preview tab that doesn't translate to dd/mm/yyyy it assumes US
> > format. I found the solution to be in the preview tab use
> > yyyy-mm-dd, it seems to be a universal format for SQL. DateTime is
> > not 'stored' in any national format, it's just a number which gets
> > formatted based on locale.
> >
> > You'll probably find it works OK when deployed!
> >
> > Chris
> >
> >
> > AshVsAOD wrote:
> >
> > > Check the code of your report. The second to last line in your
> > > XML will be: <Language>en-US</Language>
> > >
> > > Change it to:
> > >
> > > <Language>en-NZ</Language>
> > >
> > >
> > >
> > > Also ensure you have SP1 at least installed.
> > >
> > > "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in
> > > message news:F5DCEDDB-4819-4C5F-BC8B-44A4CFA80163@.microsoft.com...
> > > > Cheers Wayne
> > > >
> > > > I have run the report in the preview tab without the parameter
> > > > statement
> > > in
> > > > the where clause and i get data returned.
> > > >
> > > > The datatype of the datetime field that I need the @.StartDate
> > > > parameter to match is of smalldatetime type.
> > > >
> > > > With the @.StartDate parameter set to datetime I get data
> > > > returned, no problem there. But only if i enter the date into
> > > > the parameter box as MM/dd/yyyy. I want to be able to enter it
> > > > as dd/MM/yyyy and have it
> > > display
> > > > the correct data.
> > > >
> > > > hope this makes it a bit clearer.
> > > >
> > > > i assume i have to convert the date time just not sure on the
> > > > syntax
> > > required
> > > >
> > > >
> > > >
> > > > "Wayne Snyder" wrote:
> > > >
> > > > > Are you getting this error AFTER you set the parameter to
> > > > > datetime?
> > > > >
> > > > > It is understandable when the parameter is string, you'd have
> > > > > to format
> > > it
> > > > > correctly before sending it to SQL..
> > > > >
> > > > > --
> > > > > Wayne Snyder, MCDBA, SQL Server MVP
> > > > > Mariner, Charlotte, NC
> > > > > www.mariner-usa.com
> > > > > (Please respond only to the newsgroups.)
> > > > >
> > > > > I support the Professional Association of SQL Server (PASS)
> > > > > and it's community of SQL Server professionals.
> > > > > www.sqlpass.org
> > > > >
> > > > > "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in
> > > > > message
> > > > > news:9A05E7D1-8DA9-44DB-B5E7-5271DEDC3425@.microsoft.com...
> > > > > > Have a report that requires a @.StartDate parameter. This
> > > > > > will equal a ActualDateTime fields in a table.
> > > > > >
> > > > > > I have the following code listed in my where clause
> > > > > >
> > > > > > and (tvo.ActualDateTime = @.StartDate)
> > > > > >
> > > > > > but keeps getting throwing an error when i test. As we are
> > > > > > in New
> > > Zealand
> > > > > > our date format is dd/MM/yyyy but when entering a start
> > > > > > date in this format i
> > > > > > get an "arithmetic overflow error converting expression to
> > > > > > date type smalldatetime". I assume this is because the
> > > > > > database is storing the
> > > field
> > > > > > as
> > > > > > a datetime and its format is MM/dd/yyyy. I have set the
> > > > > > parameter to datatype datetime. I know this is probably
> > > > > > easy to sort, just need a little
> > > > > > assistance.
> > > > > >
> > > > > > Cheers.
> > > > >
> > > > >
> > > > >
> >
> >|||hi, i have this problem after i installed the SP 2 of Reporting Services,
anyone know if SP 2 modify something with the date format?
My reports use type string and not date time but with sp 1 run very well,
after the instalation of sp 2 comes the error "Arithmetic overflow error
converting expression to data type date..." when i put the parameter with the
format ddmmyyyy.
Anyone know where i can find information about this problem?
thank yoou very much!!!
Guillermo
"Chris McGuigan" wrote:
> I know what you mean! I found this out the hard way too!
> If something doesn't seem right in preview, it's often worth deploying
> and seeing if it's OK there. The rendering engine in Preview is not the
> same as in Report Manager.
> Chris
>
> Nat Johnson wrote:
> > Thanks Chris
> >
> > and you were right...works fine once deployed. just testing at
> > preview doesn't show correct date format...oh well at least it
> > works...just wish i hadn't spent so much time trying to fix
> > something i couldn't.
> >
> > have a good day...
> >
> > "Chris McGuigan" wrote:
> >
> > > Nat,
> > > If your parameter is set to datetime, then there is a bug in the
> > > preview tab that doesn't translate to dd/mm/yyyy it assumes US
> > > format. I found the solution to be in the preview tab use
> > > yyyy-mm-dd, it seems to be a universal format for SQL. DateTime is
> > > not 'stored' in any national format, it's just a number which gets
> > > formatted based on locale.
> > >
> > > You'll probably find it works OK when deployed!
> > >
> > > Chris
> > >
> > >
> > > AshVsAOD wrote:
> > >
> > > > Check the code of your report. The second to last line in your
> > > > XML will be: <Language>en-US</Language>
> > > >
> > > > Change it to:
> > > >
> > > > <Language>en-NZ</Language>
> > > >
> > > >
> > > >
> > > > Also ensure you have SP1 at least installed.
> > > >
> > > > "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in
> > > > message news:F5DCEDDB-4819-4C5F-BC8B-44A4CFA80163@.microsoft.com...
> > > > > Cheers Wayne
> > > > >
> > > > > I have run the report in the preview tab without the parameter
> > > > > statement
> > > > in
> > > > > the where clause and i get data returned.
> > > > >
> > > > > The datatype of the datetime field that I need the @.StartDate
> > > > > parameter to match is of smalldatetime type.
> > > > >
> > > > > With the @.StartDate parameter set to datetime I get data
> > > > > returned, no problem there. But only if i enter the date into
> > > > > the parameter box as MM/dd/yyyy. I want to be able to enter it
> > > > > as dd/MM/yyyy and have it
> > > > display
> > > > > the correct data.
> > > > >
> > > > > hope this makes it a bit clearer.
> > > > >
> > > > > i assume i have to convert the date time just not sure on the
> > > > > syntax
> > > > required
> > > > >
> > > > >
> > > > >
> > > > > "Wayne Snyder" wrote:
> > > > >
> > > > > > Are you getting this error AFTER you set the parameter to
> > > > > > datetime?
> > > > > >
> > > > > > It is understandable when the parameter is string, you'd have
> > > > > > to format
> > > > it
> > > > > > correctly before sending it to SQL..
> > > > > >
> > > > > > --
> > > > > > Wayne Snyder, MCDBA, SQL Server MVP
> > > > > > Mariner, Charlotte, NC
> > > > > > www.mariner-usa.com
> > > > > > (Please respond only to the newsgroups.)
> > > > > >
> > > > > > I support the Professional Association of SQL Server (PASS)
> > > > > > and it's community of SQL Server professionals.
> > > > > > www.sqlpass.org
> > > > > >
> > > > > > "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in
> > > > > > message
> > > > > > news:9A05E7D1-8DA9-44DB-B5E7-5271DEDC3425@.microsoft.com...
> > > > > > > Have a report that requires a @.StartDate parameter. This
> > > > > > > will equal a ActualDateTime fields in a table.
> > > > > > >
> > > > > > > I have the following code listed in my where clause
> > > > > > >
> > > > > > > and (tvo.ActualDateTime = @.StartDate)
> > > > > > >
> > > > > > > but keeps getting throwing an error when i test. As we are
> > > > > > > in New
> > > > Zealand
> > > > > > > our date format is dd/MM/yyyy but when entering a start
> > > > > > > date in this format i
> > > > > > > get an "arithmetic overflow error converting expression to
> > > > > > > date type smalldatetime". I assume this is because the
> > > > > > > database is storing the
> > > > field
> > > > > > > as
> > > > > > > a datetime and its format is MM/dd/yyyy. I have set the
> > > > > > > parameter to datatype datetime. I know this is probably
> > > > > > > easy to sort, just need a little
> > > > > > > assistance.
> > > > > > >
> > > > > > > Cheers.
> > > > > >
> > > > > >
> > > > > >
> > >
> > >
>

Date issue in a SQL 2005 scheduled job

I have a job in SQL 2005 that when it runs works fine if I hard code the Makedate, what I need it to do is have the Makedate equal todays date minus one day...I came up with the code below, but that does seem to work...any thoughts.

INSERT INTO abcTransaction
(
Payee,
Payment,
AccountNumber
)
SELECT
'Credit',
SUM(Rebate * Quantity),
A.AccountNumber
FROM
Account A
INNER JOIN
abdTrades T on A.AccountNumber = T.AccountNumber
Where MakeDate = getDate() - 1

GROUP BY
A.AccountNumber

Looks good. What is the error message?|||

Try:

Where MakeDate = DATEADD(day, -1, getDate())

Keep in mind, you are just subtracting one day - 24 hours. So if the underlying value for your date is 1/10/2007 15:00, then the minus one day produces 1/9/1007 15:00. So if you really want for the whole previous day try:

Where MakeDate = CAST(MONTH(DATEADD(day, - 1, GETDATE())) AS varchar) + '/' + CAST(DAY(DATEADD(day, - 1, GETDATE())) AS varchar) + '/' + CAST(YEAR(DATEADD(day, - 1, GETDATE())) AS varchar))

This is kind of messy and Ihave not found a better way. I usually wrap it into a SQL function.

|||

cloris:

Keep in mind, you are just subtracting one day - 24 hours. So if the underlying value for your date is 1/10/2007 15:00, then the minus one day produces 1/9/1007 15:00. So if you really want for the whole previous day try:

Where MakeDate = CAST(MONTH(DATEADD(day, - 1, GETDATE())) AS varchar) + '/' + CAST(DAY(DATEADD(day, - 1, GETDATE())) AS varchar) + '/' + CAST(YEAR(DATEADD(day, - 1, GETDATE())) AS varchar))

This is kind of messy and Ihave not found a better way. I usually wrap it into a SQL function.

If you need for entire previous day you can do it like this:

Where MakeDate >= convert(Varchar, Getdate() -1, 101) And MakeDate < convert(Varchar, Getdate() , 101)

|||

Will try this as it does need to be for the entire previous day, not a 24 hour cycle.

|||Much cleaner... I am filing this one away.

Date formatting very flakey

We have a couple of dates displayed on our report.
The first comes from a string in our code, and I have a format
of 'mm/dd/yyyy'
The second comes from Globals!ExecutionTime, and I have a format of 'd'
These both display the way we want (which is with leading zeros) when
we stream the report to Excel.
But when we use the ReportViewer control, or when we stream the report
to html, we get strange stuff, like
00/03/2005 (s/b 09/03/2005) in the first case,
and
42/20/2005 (s/b 02/20/2006) in the second case. (Particularly
perturbing, since this is an RS global)Does using a capital m work properly? The lowercase m is for minute.
Check to see if "MM/dd/yyyy" works properly. I haven't had a problem
with the formatting and would be inetersted if this solved your
problem.
Regards,
Dan|||Ahh..much better, thanks!

Date Formatting

My problem is with the paramater format date.

My code and data in SQL are showing the date correctly as dd/mm/yyyy.

When I run report in SSRS a couple of columns show the date mm/dd/yyyy.

The format option in all cells are set to 'd'. There is no difference between the properties on these cells.

The dates themselves seem to go wrong on the date "31/12/4000" and returns them as "12/31/4000".

Any help wqould be appreciated

You need to change the report properties language setting to: English (United Kingdom). It defaults to English(United States)

Date format in a merged field

In the footer from a report I want to print the UserID and the Date. I added a textbox with de following code: =User!UserID & " " & Globals!ExecutionTime

Now I want to change the date format in dd-MM-yyy uu:mm. This is not possible in the textbox properties because I added the UserID to the same textbox. Is there a way to change the format?

Try this:

= User!UserID & " " & Format(Globals!ExecutionTime,"dd-MM-yyyy hh:mm")

cheers,

Andrew

|||

You need to use the .NET String.Format method e.g.

=User!UserID & " " & String.Format(Globals!ExecutionTime, "dd-MM-yyy uu:mm")

|||Thanks a lot! It works!

Date Convertion

I am currently running a DTS package to extract data from a DB2 database.
The code reads Select * from ABC where entrydate='12/15/2005'
This works fine but I need to automate the process by selecting the date
automatically. As soon as the entrydate = formula the extract do not work.
I have tried different versions of date formula
The format of the date field on the SQL table is smalldatetime and on DB2
it is date
Can some one helpVuka
Use 'yyyymmdd' format with SQL Server
Lookup CONVERT system function in the BOL
"Vuka" <Vuka@.discussions.microsoft.com> wrote in message
news:CF33E355-6FFA-4C1F-95A8-C9EABE0CDD29@.microsoft.com...
>I am currently running a DTS package to extract data from a DB2 database.
> The code reads Select * from ABC where entrydate='12/15/2005'
> This works fine but I need to automate the process by selecting the date
> automatically. As soon as the entrydate = formula the extract do not
> work.
> I have tried different versions of date formula
> The format of the date field on the SQL table is smalldatetime and on DB2
> it is date
> Can some one help

Date Conversion

code:
SET @.Yr = 1986
Set @.Datum = '1/1/' & @.Yr
Hi, I am experiencing a problem with the "Set @.Datum " statement. Does
anyone know how would I use the cast function to convert this, " '1/1/'
& @.Yr " to be put into my DateTime var,@.Datum?
Thanx.See my reply to your other post. No need to post the same question in three threads.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"amatuer" <njoosub@.gmail.com> wrote in message
news:1163143392.775333.101750@.h48g2000cwc.googlegroups.com...
> code:
> SET @.Yr = 1986
> Set @.Datum = '1/1/' & @.Yr
>
> Hi, I am experiencing a problem with the "Set @.Datum " statement. Does
> anyone know how would I use the cast function to convert this, " '1/1/'
> & @.Yr " to be put into my DateTime var,@.Datum?
>
> Thanx.
>|||try this
SET @.Yr = 1986
Set @.Datum = '1/1/' + cast( @.Yr as varchar)
vt
"amatuer" <njoosub@.gmail.com> wrote in message
news:1163143392.775333.101750@.h48g2000cwc.googlegroups.com...
> code:
> SET @.Yr = 1986
> Set @.Datum = '1/1/' & @.Yr
>
> Hi, I am experiencing a problem with the "Set @.Datum " statement. Does
> anyone know how would I use the cast function to convert this, " '1/1/'
> & @.Yr " to be put into my DateTime var,@.Datum?
>
> Thanx.
>

Date an Time conversion problem

Well here is the problem iam trying to evaluate a expresion and return a string but when i run my code it always return where my expresion is false where it should return true. here is my code.

BEGIN
DECLARE @.datefin_flag char(13), @.strip datetime
select @.strip = getdate()
--select convert(char(10),@.strip,120)
select @.datefin_flag = dateend FROM mattstest WHERE convert(char(10),datebegin,120) <= convert(char(10),@.strip,120) and convert(char(10),dateend,120) >= convert(char(10),@.strip,120)
--select @.datefin_flag
--UPDATE dateflagevent SET flagevent = getdate() FROM dateflagevent
IF (@.datefin_flag = @.strip)
BEGIN
print 'Run'
END
ELSE
print 'You cant run this'
END

Now here is the my table data:

datebegin datefin
-------- --------
2004-12-25 00:00:00.000 2005-01-25 00:00:00.000
2004-11-25 00:00:00.000 2004-12-24 00:00:00.000
2005-02-25 00:00:00.000 2005-03-25 00:00:00.000

I think that the problem is the date and time they are the same but not in the right format its like saying 2004-01-25 is equal to janv 25 2005 how do i correct this.your problem might be you are doing a >= comparison on character data due to your convert functions|||Can you show me in code.|||I am going to be brief because I am about to go home and I am tired of goofing off at work on this forum today but I think your problem is that you want to compare two dates but your are converting your date values to character strings.

convert(char(10),datebegin,120) <= convert(char(10),@.strip,120) and convert(char(10),dateend,120) >= convert(char(10),@.strip,120)

I believe when you do this (I have not double checked so flame me all you want smart guys) you are actually comparing the unicode value of the 2 strings you just created.|||Correct, except that format 120 is "yyyy-mm-dd", so that even as character data it should sort correctly. (I'll need to double-check that in the morning...)

I think your problem is that you are assigning the value getdate() to @.strip, and getdate() includes both a date AND a time value. So later when you compare it to the value selected from your table (dateend? datefin?), it is only going to match if run at precisely midnight.

Try this assignment:
select @.strip = CONVERT(CHAR(10), getdate(), 120)|||I think it realy is the time associated with the date but how do i strip it out or how do i evaluate my expression ?|||select @.strip = CONVERT(CHAR(10), getdate(), 120).

select @.strip = CONVERT(CHAR(10), getdate(), 120).

select @.strip = CONVERT(CHAR(10), getdate(), 120).

select @.strip = CONVERT(CHAR(10), getdate(), 120).

select @.strip = CONVERT(CHAR(10), getdate(), 120).

select @.strip = CONVERT(CHAR(10), getdate(), 120).

select @.strip = CONVERT(CHAR(10), getdate(), 120).

select @.strip = CONVERT(CHAR(10), getdate(), 120).

select @.strip = CONVERT(CHAR(10), getdate(), 120).

select @.strip = CONVERT(CHAR(10), getdate(), 120).

select @.strip = CONVERT(CHAR(10), getdate(), 120).

select @.strip = CONVERT(CHAR(10), getdate(), 120).

select @.strip = CONVERT(CHAR(10), getdate(), 120).

select @.strip = CONVERT(CHAR(10), getdate(), 120).

select @.strip = CONVERT(CHAR(10), getdate(), 120).

select @.strip = CONVERT(CHAR(10), getdate(), 120).|||instead of converting datebegin and dateend to strings (which will mean that any indexes on those columns will be ignored, you get a table scan), why not do the query based on datetime comparisons, based on the current date which you can obtain by stripping the time portion out of getdate() while leaving the result as a datetime value

specifically,
-- strip time component from today, but leave as datetime
select @.strip = dateadd(d,datediff(d,0,getdate()),0)

-- search based on today's date
select @.datefin_flag = dateend
from mattstest
where datebegin <= @.strip
and dateend >= @.strip|||thanx dude worked perfect|||select @.strip = dateadd(d,datediff(d,0,getdate()),0).

select @.strip = dateadd(d,datediff(d,0,getdate()),0).

select @.strip = dateadd(d,datediff(d,0,getdate()),0).

select @.strip = dateadd(d,datediff(d,0,getdate()),0).

select @.strip = dateadd(d,datediff(d,0,getdate()),0).

select @.strip = dateadd(d,datediff(d,0,getdate()),0).
.
.
.

Date a Balance went to Zero

In my report code, I am pulling pva.InsBalance + pva.patbalance AS TotalVisitBalance,

What I need is, to know the date in which this balance went to $0.00. Is it possible to script this anyhow?

Report Code

set nocount on

declare @.startdate datetime,
@.enddate datetime,
@.ticketnumber varchar(20)

set @.ticketnumber = CAST(NULL as VARCHAR(20))
set @.startdate = ISNULL(NULL,'1/1/1900')
set @.enddate = DATEADD(DAY,1,ISNULL(NULL,'1/1/3000'))

SELECT ic.ListName AS CarrierName,
pva.InsBalance + pva.patbalance AS TotalVisitBalance,
ic.address1 as CarrierAddress,
ic.city as CarrierCity,
ic.state as CarrierState,
ic.zip as CarrierZip,
icc.ClaimPayerId,
pv.Ticketnumber,
CONVERT(VARCHAR,pv.visit,101) as DateOfService,
CONVERT(VARCHAR,pv.firstfileddate,101)AS FirstFiled,
'DaysBetween'= DATEDIFF(DD,pv.visit,pv.firstfileddate),
CONVERT(VARCHAR,pv.lastfileddate,101)AS LastFiled,
pp.last+', '+pp.first as PatientName,
pp.PatientID,
ec.Charges as VisitChargesFiled,
ec.Procedures as VisitProceduresFiled,
CONVERT(VARCHAR,ecf.FileTransmitted,101)AS FileTransmitted,
CAST(NULL as DATETIME) as ClaimPrinted,
ecf.FiledBy,
ecf.SubmissionNumber,
ecf.name as ClaimFileName,
ch.ClearinghouseName,
fm.description as FilingMethod,
'Electronic' as FilingType

into #temp

FROM EDIClaimFile ecf
INNER JOIN EDIClaim ec ON ecf.EDIClaimFileId = ec.EDIClaimFileId
INNER JOIN InsuranceCarriers ic ON ec.InsuranceCarriersId = ic.InsuranceCarriersId
INNER JOIN InsuranceCarrierCompany icc ON ic.InsuranceCarriersId = icc.InsuranceCarriersId
INNER JOIN patientvisit pv on ec.patientvisitID = pv.patientvisitID
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
INNER JOIN patientprofile pp on pv.patientprofileID = pp.patientprofileID
LEFT JOIN (select * from medlists where tablename= 'FilingMethods') fm on ec.filingmethodMID = fm.medlistsID
INNER JOIN clearinghouse ch on ecf.clearinghouseID = ch.clearinghouseID

WHERE ecf.FileTransmitted >= @.startdate
AND ecf.FileTransmitted < @.enddate
AND --Filter on ticket
(
(NULL IS NOT NULL AND pv.ticketnumber = @.ticketnumber) OR
(NULL IS NULL)
)
AND --Filter on company
(
(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on facility
(
(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Carrier
(
(NULL IS NOT NULL AND ec.insurancecarriersID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Provider
(
(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Patient
(
(NULL IS NOT NULL AND pv.PatientProfileID IN (NULL)) OR
(NULL IS NULL)
)

-- Paper Claims
INSERT INTO #temp (Carriername,TotalVisitBalance, --CarrierAddress, CarrierCity, CarrierState, CarrierZip,
ticketnumber, dateofservice, firstfiled, DaysBetween, lastfiled, patientname, patientID, visitchargesfiled,
visitproceduresfiled, ClaimPrinted, filedby, claimfilename, clearinghousename,
filingmethod, FilingType)

SELECT ISNULL(pvpc.Name,'No Carrier') AS CarrierName,
pva.InsBalance + pva.patbalance AS TotalVisitBalance,
pv.Ticketnumber,
CONVERT(VARCHAR,pv.visit,101) as DateOfService,
CONVERT(VARCHAR,pv.firstfileddate,101)AS FirstFiled,
'DaysBetween'= DATEDIFF(DD,pv.visit,pv.firstfileddate),
CONVERT(VARCHAR,pv.lastfileddate,101)AS LastFiled,
pp.last+', '+pp.first as PatientName,
pp.PatientID,
pvpc.Charges as VisitChargesFiled,
pvpc.Procedures as VisitProceduresFiled,
pvpc.created as ClaimPrinted,
pvpc.createdby as FiledBy,
'Paper' as claimfilename,
'' as clearinghousename,
fm.description as FilingMethod,
'Paper' as FilingType

FROM PatientvisitPaperClaim pvpc
INNER JOIN patientvisit pv on pvpc.patientvisitID = pv.patientvisitID
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
INNER JOIN patientprofile pp on pv.patientprofileID = pp.patientprofileID
LEFT JOIN (select * from medlists where tablename= 'FilingMethods') fm on pvpc.filingmethodMID = fm.medlistsID

WHERE pvpc.created >= @.startdate
AND pvpc.created < @.enddate
AND --Filter on ticket
(
(NULL IS NOT NULL AND pv.ticketnumber = @.ticketnumber) OR
(NULL IS NULL)
)
AND --Filter on company
(
(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on facility
(
(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
(NULL IS NULL)
)
-- AND --Filter on Carrier
-- (
-- (NULL IS NOT NULL AND ec.insurancecarriersID IN (NULL)) OR
-- (NULL IS NULL)
-- )
AND --Filter on Provider
(
(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Patient
(
(NULL IS NOT NULL AND pv.PatientProfileID IN (NULL)) OR
(NULL IS NULL)
)

IF '1' = '1'
BEGIN
select *
from #temp
order by ticketnumber
END

IF '1' = '2'
BEGIN
select *
from #temp
where filingtype = 'Electronic'
order by ticketnumber
END

IF '1' = '3'
BEGIN
select *
from #temp
where filingtype = 'Paper'
order by ticketnumber
END

drop table #temp

I haven't gone right through your code yet, but... Can you wrap up your main query section into a table expression and then put something like 'select min(somedate) from (yourbigquery) q where q.totalvisitbalance >= 0"

Actually - it looks like you're populating #temp, so you could do a similar thing there:

select min(somedate) from #temp where totalvisitbalance >= 0

I notice that you're doing this: "CONVERT(VARCHAR,pv.visit,101) as DateOfService"... I wouldn't, because then you can't use things like 'min' on it. Don't convert it to varchar. You should still be able to display it nicely in your report (pick your format in your report, not in the query), but by not converting it, the database will still understand it as a date.

Rob