asp / sql server dates in recordsets

Hey,

I've got an Classic ASP app pulling some records out of a DB, which
worked fine when it was Access. When we upped to SQL Server, all of a
sudden, when the Recordsets came back, all the dates were empty, even
though the data was showing up in the DB.

About a year ago (only code asp apps once and a while), I remember
seeing this and finding something that just had something to do with the
order in which something was being referenced, but I just can't for the
life of me remember what it was.

The Dates are set as datetime, length of 8.

Anyone know why this happens and a solution?

many thanks,

rob"rob" <rob@.pleaseremovespamcherny.com> wrote in message
news:XYadnScIEbMPLpvdRVn-uA@.giganews.com...
> Hey,
> I've got an Classic ASP app pulling some records out of a DB, which
> worked fine when it was Access. When we upped to SQL Server, all of a
> sudden, when the Recordsets came back, all the dates were empty, even
> though the data was showing up in the DB.
> About a year ago (only code asp apps once and a while), I remember
> seeing this and finding something that just had something to do with the
> order in which something was being referenced, but I just can't for the
> life of me remember what it was.
> The Dates are set as datetime, length of 8.
> Anyone know why this happens and a solution?
> many thanks,
> rob

It's not really possible to give a good answer without more information. Do
you have an example of a query which returns data in Query Analyzer, but not
in ASP?

You might also look at www.aspfaq.com, if you haven't already - there are
quite a lot of articles about handling dates.

Simon

Arrays in SQL server

I am creating an application for booking events. Each event has several dates and each date has a fixed amount of available seats.

Currently in the events table I have a field for the number of available seats and a field for the string of dates that are later parsed into a listbox control.

The problem with my current setup is each date is sharing the same number of available seats so if date Event A is decemented 10 so will Event B.

I need some way to associate each date with it's own number of available seats. What is the best way to do this? An array? If so how do I store an array in the DB?

Thanks, Justin.::I need some way to associate each date with it's own number of available seats. What is
::the best way to do this? An array?

By NO means.

The best way is to apply proper standard procedures for REALATIONAL databases. There is something called "normalization", which, btw., is explained in EVERY beginner book about databases.

Som ,y advice is: your best way is to shell out some money and read a ookabout how a database works. Something simple, like "SQL for Dummies".

You will end up with multiple tables, and an event being on multiple occations will have multiple entries, one for every date.

This:

::and a field for the string of dates that are later parsed into a listbox control.

is something that you better make sure your boss does not see. I would terminate everyone doing something like this immediately for gross incompetence in 99.99% of the cases. The relational model is VERY clear with how things like this should be modelled, and has been so for the last 30-sth years. Your model is denormalized and it is sure you run into trouble.

Really, get a beginner book about databases.|||As Thona states - Normalization is the key.
when you build a denormalized database, your asking for trouble.

With limited knowledge of what your trying to accomplish, I would envision a structure more like this...

tblEvent
--EventId (autonumber)
--EventDescription

tblEventInstance
--EventInstanceId (autonumber)
--EventId (integer)
--EventDate (date/time)
--EventSeats (integer)

tblEventSignUp
--EventInstanceId (integer)
--Person

etc...

anytime you're packing multiple pieces of data into a single column you gotta ask yourself "why?"

Arithmetic Overflow converting date error with UK dates but not with US dates.

Hi there,
My problem is that I have a DateTo dataset that receives a DateFrom
parameter. The default value of the DateFrom param is taken from a
DateDefault parameter value.
However, when I run the DateTo dataset, entering 27/03/2007, I get an
arithmetic overflow conversion error. If I enter US date 03/27/2007,
it works.
I am not sure why this happens because the dates use ISO (112) or ANSI
(102) formatting - I have tried with both of these.
I think it's to do with the conversion of dates, but I have not been
able to get a working version that does not use US dates.
DateFrom:
with cte as (
select distinct convert(varchar, starttime, 112) as DateFrom
from dbo.sysdtslog90 with (nolock)
)
select
convert(datetime, DateFrom) as DateValue
,DateFrom as DateLabel
from
cte
order by
1 desc
DateDefault:
with cte as (
select max(starttime) as DateDefault
from dbo.sysdtslog90 with (nolock)
)
select
convert(datetime, convert(varchar, DateDefault, 112)) as DateValue
from
cte
DateTo:
with cte as (
select distinct convert(varchar, starttime, 112) as DateTo
from dbo.sysdtslog90 with (nolock)
where starttime >= @.DateFrom -- replace with '27/03/2007' fails --
replace with '03/27/2007' succeeds
)
select
convert(datetime, DateTo) as DateValue
,DateTo as DateLabel
from
cte
order by
1 desc
I have attached a really simple RDL file that shows the problem. To
use it, simply change the datasource to any DB that has the standard
SSIS sysdtslog90 table in. This is automatically generated on the DB
when you turn on logging in SSIS to log to sql server.
Thanks in advance for any help!
RDL FILE
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/
2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/
SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="MyLoggingDB">
<DataSourceReference>MyLoggingDB</DataSourceReference>
<rd:DataSourceID>518ad1ee-288c-4957-a44d-408e39de2244</
rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>2.5cm</BottomMargin>
<RightMargin>2.5cm</RightMargin>
<PageWidth>21cm</PageWidth>
<ReportParameters>
<ReportParameter Name="DateFrom">
<DataType>String</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>DateDefault</DataSetName>
<ValueField>DateValue</ValueField>
</DataSetReference>
</DefaultValue>
<Prompt>Date From</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>DateFrom</DataSetName>
<ValueField>DateValue</ValueField>
<LabelField>DateLabel</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>21cm</InteractiveWidth>
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ColumnSpacing>1cm</ColumnSpacing>
<Height>5cm</Height>
</Body>
<rd:ReportID>fabcf51f-0565-4b9b-8a68-693ee7ea7e39</rd:ReportID>
<LeftMargin>2.5cm</LeftMargin>
<DataSets>
<DataSet Name="DateFrom">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>with cte as (
select distinct convert(varchar, starttime, 112) as DateFrom
from dbo.sysdtslog90 with (nolock)
)
select
convert(datetime, DateFrom) as DateValue
,DateFrom as DateLabel
from
cte
order by
1 desc</CommandText>
<DataSourceName>MyLoggingDB</DataSourceName>
</Query>
<Fields>
<Field Name="DateValue">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>DateValue</DataField>
</Field>
<Field Name="DateLabel">
<rd:TypeName>System.String</rd:TypeName>
<DataField>DateLabel</DataField>
</Field>
</Fields>
</DataSet>
<DataSet Name="DateDefault">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>with cte as (
select max(starttime) as DateDefault
from dbo.sysdtslog90 with (nolock)
)
select
convert(datetime, convert(varchar, DateDefault, 112)) as DateValue
from
cte</CommandText>
<DataSourceName>MyLoggingDB</DataSourceName>
</Query>
<Fields>
<Field Name="DateValue">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>DateValue</DataField>
</Field>
</Fields>
</DataSet>
<DataSet Name="DateTo">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>with cte as (
select distinct convert(varchar, starttime, 112) as DateTo
from dbo.sysdtslog90 with (nolock)
where starttime >= @.DateFrom
)
select
convert(datetime, DateTo) as DateValue
,DateTo as DateLabel
from
cte
order by
1 desc</CommandText>
<QueryParameters>
<QueryParameter Name="@.DateFrom">
<Value>=Parameters!DateFrom.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>MyLoggingDB</DataSourceName>
</Query>
<Fields>
<Field Name="DateValue">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>DateValue</DataField>
</Field>
<Field Name="DateLabel">
<rd:TypeName>System.String</rd:TypeName>
<DataField>DateLabel</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>16cm</Width>
<InteractiveHeight>29.7cm</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>2.5cm</TopMargin>
<PageHeight>29.7cm</PageHeight>
</Report>To answer my own question and save others wasting time.
I have solved this issue now, by converting all the dates to use ISO
standard 20070327 format and pass this around to the parameters
instead of the full date.
Thanks
On 27 Mar, 17:54, "weelin" <wee...@.gmail.com> wrote:
> Hi there,
> My problem is that I have a DateTo dataset that receives a DateFrom
> parameter. The default value of the DateFrom param is taken from a
> DateDefault parameter value.
> However, when I run the DateTo dataset, entering 27/03/2007, I get an
> arithmetic overflow conversion error. If I enter US date 03/27/2007,
> it works.
> I am not sure why this happens because the dates use ISO (112) or ANSI
> (102) formatting - I have tried with both of these.
> I think it's to do with the conversion of dates, but I have not been
> able to get a working version that does not use US dates.
> DateFrom:
> with cte as (
> select distinct convert(varchar, starttime, 112) as DateFrom
> from dbo.sysdtslog90 with (nolock)
> )
> select
> convert(datetime, DateFrom) as DateValue
> ,DateFrom as DateLabel
> from
> cte
> order by
> 1 desc
> DateDefault:
> with cte as (
> select max(starttime) as DateDefault
> from dbo.sysdtslog90 with (nolock)
> )
> select
> convert(datetime, convert(varchar, DateDefault, 112)) as DateValue
> from
> cte
> DateTo:
> with cte as (
> select distinct convert(varchar, starttime, 112) as DateTo
> from dbo.sysdtslog90 with (nolock)
> where starttime >= @.DateFrom -- replace with '27/03/2007' fails --
> replace with '03/27/2007' succeeds
> )
> select
> convert(datetime, DateTo) as DateValue
> ,DateTo as DateLabel
> from
> cte
> order by
> 1 desc
> I have attached a really simple RDL file that shows the problem. To
> use it, simply change the datasource to any DB that has the standard
> SSIS sysdtslog90 table in. This is automatically generated on the DB
> when you turn on logging in SSIS to log to sql server.
> Thanks in advance for any help!
> RDL FILE
> <?xml version="1.0" encoding="utf-8"?>
> <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/
> 2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/
> SQLServer/reporting/reportdesigner">
> <DataSources>
> <DataSource Name="MyLoggingDB">
> <DataSourceReference>MyLoggingDB</DataSourceReference>
> <rd:DataSourceID>518ad1ee-288c-4957-a44d-408e39de2244</
> rd:DataSourceID>
> </DataSource>
> </DataSources>
> <BottomMargin>2.5cm</BottomMargin>
> <RightMargin>2.5cm</RightMargin>
> <PageWidth>21cm</PageWidth>
> <ReportParameters>
> <ReportParameter Name="DateFrom">
> <DataType>String</DataType>
> <DefaultValue>
> <DataSetReference>
> <DataSetName>DateDefault</DataSetName>
> <ValueField>DateValue</ValueField>
> </DataSetReference>
> </DefaultValue>
> <Prompt>Date From</Prompt>
> <ValidValues>
> <DataSetReference>
> <DataSetName>DateFrom</DataSetName>
> <ValueField>DateValue</ValueField>
> <LabelField>DateLabel</LabelField>
> </DataSetReference>
> </ValidValues>
> </ReportParameter>
> </ReportParameters>
> <rd:DrawGrid>true</rd:DrawGrid>
> <InteractiveWidth>21cm</InteractiveWidth>
> <rd:GridSpacing>0.25cm</rd:GridSpacing>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <Body>
> <ColumnSpacing>1cm</ColumnSpacing>
> <Height>5cm</Height>
> </Body>
> <rd:ReportID>fabcf51f-0565-4b9b-8a68-693ee7ea7e39</rd:ReportID>
> <LeftMargin>2.5cm</LeftMargin>
> <DataSets>
> <DataSet Name="DateFrom">
> <Query>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> <CommandText>with cte as (
> select distinct convert(varchar, starttime, 112) as DateFrom
> from dbo.sysdtslog90 with (nolock)
> )
> select
> convert(datetime, DateFrom) as DateValue
> ,DateFrom as DateLabel
> from
> cte
> order by
> 1 desc</CommandText>
> <DataSourceName>MyLoggingDB</DataSourceName>
> </Query>
> <Fields>
> <Field Name="DateValue">
> <rd:TypeName>System.DateTime</rd:TypeName>
> <DataField>DateValue</DataField>
> </Field>
> <Field Name="DateLabel">
> <rd:TypeName>System.String</rd:TypeName>
> <DataField>DateLabel</DataField>
> </Field>
> </Fields>
> </DataSet>
> <DataSet Name="DateDefault">
> <Query>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> <CommandText>with cte as (
> select max(starttime) as DateDefault
> from dbo.sysdtslog90 with (nolock)
> )
> select
> convert(datetime, convert(varchar, DateDefault, 112)) as DateValue
> from
> cte</CommandText>
> <DataSourceName>MyLoggingDB</DataSourceName>
> </Query>
> <Fields>
> <Field Name="DateValue">
> <rd:TypeName>System.DateTime</rd:TypeName>
> <DataField>DateValue</DataField>
> </Field>
> </Fields>
> </DataSet>
> <DataSet Name="DateTo">
> <Query>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> <CommandText>with cte as (
> select distinct convert(varchar, starttime, 112) as DateTo
> from dbo.sysdtslog90 with (nolock)
> where starttime >= @.DateFrom
> )
> select
> convert(datetime, DateTo) as DateValue
> ,DateTo as DateLabel
> from
> cte
> order by
> 1 desc</CommandText>
> <QueryParameters>
> <QueryParameter Name="@.DateFrom">
> <Value>=Parameters!DateFrom.Value</Value>
> </QueryParameter>
> </QueryParameters>
> <DataSourceName>MyLoggingDB</DataSourceName>
> </Query>
> <Fields>
> <Field Name="DateValue">
> <rd:TypeName>System.DateTime</rd:TypeName>
> <DataField>DateValue</DataField>
> </Field>
> <Field Name="DateLabel">
> <rd:TypeName>System.String</rd:TypeName>
> <DataField>DateLabel</DataField>
> </Field>
> </Fields>
> </DataSet>
> </DataSets>
> <Width>16cm</Width>
> <InteractiveHeight>29.7cm</InteractiveHeight>
> <Language>en-US</Language>
> <TopMargin>2.5cm</TopMargin>
> <PageHeight>29.7cm</PageHeight>
> </Report>

Arithmetic overflow

Hi,
Every time a run a query where dates are included, I get an 'Arithmetic
overflow error converting expression to data type datetime'.
The dates are stored as yyyymmdd (nvarchar 8) and usually the 'month(DATE_X)
AS Month' syntax is used.
I think that somehow, some of the dates are corrupted.
How can I found out which of then are corrupted? There're too many to scroll
through.
TIA,
Ana
************************************************** *****************************
"Eres dueo de lo que callas y esclavo de lo que dices"
"Judge your success by what you had to give up in order to get it"
************************************************** *****************************
On Tue, 28 Sep 2004 23:22:39 +0200, Ana wrote:

>The dates are stored as yyyymmdd (nvarchar 8) and usually the 'month(DATE_X)
>AS Month' syntax is used.
>I think that somehow, some of the dates are corrupted.
>How can I found out which of then are corrupted?
Hi Ana,
SELECT YourDateCol
FROM YourTable
WHERE ISDATE(YourDateCol) = 0
And change your database design: use the datetime format to store dates
instead. <g>
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Arithmetic Operation with Dates

Hi I'm trying to get the difference between two dates. I need to create a Calculated member to show it. I'm using this formula

PeriodDays = LastDate - FirstDate

For example, I have a Time Dimension, I choose two dates:

FirstDate = 07/04/2006 it's identity is 300

LastDate = 15/04/2006 it's identity is 308

PeriodDays = 308 - 300 = 8 this value I need to show in a Calculated Member

The Time dimension has three levels

Year, month and date

What happens when I choose only a month?

Thanks

Dear,

I dont understand onething u are talking about date diffrence and u are giving an identity example.

Pls explain in detail.

Regards

Sufian

|||

Hi, ohh, I will try to explain again

I need to get how many days are between two dates, For example, I choose two dates from my Time dimension

Date1 = 07/04/2006

Date2 = 15/04/2006

Days = Date2 - Date1 = 15/04/2006 - 07/04/2006 = 8 days and this value I need to show in a Calculated member

Is it possible?

My time dimension has three levels

Year, Month and day

Can I do this operation, with the month level?

Regards

Thanks

Are there MIN(A,B) or MAX(A,B) functions in SQL?

Just wondering, since I came across a case where I have to select the MIN of 2 dates, or MAX of 2 dates

say I want

min_date = MIN(dateA, dateB)

max_date = MAX(dateA, dateB)

I can probably do it with CASE statement, but that seems a bit clumsy

like min_date = CASE WHEN dateA < dateB THEN dateA ELSE dateB END

Thanks

If I were you, I would write a function that returns the min or max of the two and call that function in your statement. Like this:

create function dbo.udf_maxdate
(
@.date1 datetime,
@.date2 datetime
)
returns datetime
as
begin
declare @.returndate datetime
set @.returndate = case when @.date1>@.date2 then @.date1 else @.date2 end
return(@.returndate)
end|||

Unfortunately, no.

You could create your own. Perhaps passing in an array of values (as a string/varchar). Then using something like Jen Suessmeyer's Split function, move the array values into a table variable, use Min() or Max() and return the appropriate value.

Split Function (Jens Suessmeyer)
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

|||

Thanks guys

I just used CASE statements, good enough for the one-time fix