advice for storing reservation/booking date ranges...

I am building a small app that will display availability data for properties. I have a calendar that displays the dates for a whole year in month rows with each days colour representing the availability status, i.e. booked, on hold etc.

My question is about how to store these dates in the db. At the moment I have the table below:

TABLE Availability
[PropertyID] [int] NOT NULL ,
[StatusID] [tinyint] NOT NULL ,
[StartDate] [smalldatetime] NOT NULL ,
[EndDate] [smalldatetime] NOT NULL

I was planning on having four status's for any given date, unknown, available, on hold or booked.

Displaying the dates has proved pretty simple but updating availability means I would need to query the db to see if any of the dates overlapped, I would then have to add the new date range/status as well as change any date ranges that overlapped, either in the sp or in the code and this is what made me wonder if there was a better way.

Does this sound a reasonable approach? Any advice or pointers would be greatly appreciated, This is the first time I have had to store date ranges and I want to make sure I am doing it right.

Instead of an Availability table, I would think you'd want a Booked table. Assume the property is available and create records in the Booked table when it's booked or held. Don't create records if you'd have an overlap by doing so. If no record for a time frame, then it's available.

It seems the way you're doing it, you would have to create a record for every date for every property. That makes it tough to be more granular if you need to go to the hour or minute level.

|||

Yes, unfortunately I have to cater for users who are not keeping the calendar uptodate hence using both the unknown and available status.

The default will be unknown, so anything not in the table will be that status and then it is up to users to use the calendar to update the properties availability, so there are three status's, available, on-hold and booked. These three will be in the table.

I think I will proceed as is and see how I go...thanks for the input.

Advanced Defaulting Data Parameters

I have to date parameters defaulted as follows:
startdate = dateadd("M",-1,Today())
enddate = today()
So it will display data for the past month from today. Now, what I
need is the following. I need a subscription to be sent out at the
start of the month (so June 1st would be the next one). I need it to
show all data for May (that's 31 days, so far this is doing 30 days).
So using this same logic, it will return May 2nd to May 31st. I need
it to return the first and last of the previous month.
ThanksThere is a great article on how to do date stuff in T-SQL that might help
with other ideas, as the functionally is the same in VB
http://www.databasejournal.com/features/mssql/article.php/3076421
Reeves
"Ayman" wrote:
> I have to date parameters defaulted as follows:
> startdate = dateadd("M",-1,Today())
> enddate = today()
> So it will display data for the past month from today. Now, what I
> need is the following. I need a subscription to be sent out at the
> start of the month (so June 1st would be the next one). I need it to
> show all data for May (that's 31 days, so far this is doing 30 days).
> So using this same logic, it will return May 2nd to May 31st. I need
> it to return the first and last of the previous month.
> Thanks
>|||Here's how to do it in T-SQL (you can add this as a little dataset into your
report) -- you'll have to adjust the string parsing in the nested select if
your server's date representation is not MMDDYYY, but it's got three parts
and they are all straightforward. The only exciting bit there is making
sure to account for the first month of the year when you put the date
together.
SELECT CAST(FirstOfLastMonth AS DATETIME) AS FirstOfPreviousMonth,
DATEADD(month,1,CAST(FirstOfLastMonth AS DATETIME))-1 AS LastOfPreviousMonth
FROM (
SELECT FirstOfLastMonth = CASE WHEN MONTH(GETDATE()) = 1 THEN '12'
ELSE CAST(MONTH(GETDATE()) -1 AS VARCHAR) END +
'/1/' +
CASE WHEN MONTH(GETDATE()) = 1 THEN CAST(YEAR(GETDATE())-1 AS VARCHAR)
ELSE CAST(YEAR(GETDATE()) AS VARCHAR) END) X
Here's the trick: get the first day of the last month first (that's the
nested select) because you know its day is always day #1 of that month.
Once you've done that, getting the *last* day of the previous month is
pretty easy, no matter how many days there are in the month! Just use
DATEADD to go forward one month from the first day, which you've already
derived, and then go back one day <g>. You are probably going to slap
yourself on the head when you read that <g>.
>L<
"Ayman" <aymantg@.gmail.com> wrote in message
news:1180453077.604629.38770@.o5g2000hsb.googlegroups.com...
>I have to date parameters defaulted as follows:
> startdate = dateadd("M",-1,Today())
> enddate = today()
> So it will display data for the past month from today. Now, what I
> need is the following. I need a subscription to be sent out at the
> start of the month (so June 1st would be the next one). I need it to
> show all data for May (that's 31 days, so far this is doing 30 days).
> So using this same logic, it will return May 2nd to May 31st. I need
> it to return the first and last of the previous month.
> Thanks
>|||On May 30, 6:52 pm, "Lisa Slater Nicholls" <l...@.spacefold.com> wrote:
> Here's how to do it in T-SQL (you can add this as a little dataset into your
> report) -- you'll have to adjust the string parsing in the nested select if
> your server's date representation is not MMDDYYY, but it's got three parts
> and they are all straightforward. The only exciting bit there is making
> sure to account for the first month of the year when you put the date
> together.
> SELECT CAST(FirstOfLastMonth AS DATETIME) AS FirstOfPreviousMonth,
> DATEADD(month,1,CAST(FirstOfLastMonth AS DATETIME))-1 AS LastOfPreviousMonth
> FROM (
> SELECT FirstOfLastMonth => CASE WHEN MONTH(GETDATE()) = 1 THEN '12'
> ELSE CAST(MONTH(GETDATE()) -1 AS VARCHAR) END +
> '/1/' +
> CASE WHEN MONTH(GETDATE()) = 1 THEN CAST(YEAR(GETDATE())-1 AS VARCHAR)
> ELSE CAST(YEAR(GETDATE()) AS VARCHAR) END) X
> Here's the trick: get the first day of the last month first (that's the
> nested select) because you know its day is always day #1 of that month.
> Once you've done that, getting the *last* day of the previous month is
> pretty easy, no matter how many days there are in the month! Just use
> DATEADD to go forward one month from the first day, which you've already
> derived, and then go back one day <g>. You are probably going to slap
> yourself on the head when you read that <g>.
> >L<
> "Ayman" <ayma...@.gmail.com> wrote in message
> news:1180453077.604629.38770@.o5g2000hsb.googlegroups.com...
> >I have to date parameters defaulted as follows:
> > startdate = dateadd("M",-1,Today())
> > enddate = today()
> > So it will display data for the past month from today. Now, what I
> > need is the following. I need a subscription to be sent out at the
> > start of the month (so June 1st would be the next one). I need it to
> > show all data for May (that's 31 days, so far this is doing 30 days).
> > So using this same logic, it will return May 2nd to May 31st. I need
> > it to return the first and last of the previous month.
> > Thanks
I took a slightly different approach but it worked great. I made two
data sets with the following code.
end of month dataset:
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))
start of month dataset:
select DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0)
Under Report>Report Parameters I just used the query for the default
values. Since both scripts only return one value, there is nothing
else that can be defaulted for either of these parameters. Works
well, although the first day of the month only puts the date and not
the time, it does start from midnight though. Thanks for everyone's
help.|||>>I made two data sets with the following code.
You can definitely do that -- and I'm glad you have something that works out
for you!
FWIW I like to have a single dataset with a whole bunch of parameter values
in it for reference (IOW a dataset that always has one row, with each
parameter or configuration value expressed as a column. That way, if you
need a lot of them, you don't have a lot of little datasets littering
around, and you can add in more columns with (say) formatted versions for
display in the report header, without a lot of work.
For example -- most of what you see here is just used in the report title,
but it's convenient to make it available to the report this way:
SELECT CONVERT(VARCHAR,@.From,101) AS FromDate,
CONVERT(VARCHAR,@.To,101) AS ToDate,
Left(DateName(month,@.From),3) AS FromCMonth,
Left(DateName(month,@.To),3) AS ToCMonth,
DATEPART(quarter,@.From) AS FromQtr,
DATEPART(quarter,@.To) AS ToQtr,
CAST(DATEPART(year,@.From) AS VARCHAR(4)) + '-' +
DateName(week,@.From) AS FromWeek,
CAST(DATEPART(year,@.To) AS VARCHAR(4)) + '-' +
DateName(week,@.To) AS ToWeek
...just a personal style thing, but it works really well...
>L<
"Ayman" <aymantg@.gmail.com> wrote in message
news:1180613135.364413.119400@.q75g2000hsh.googlegroups.com...
> On May 30, 6:52 pm, "Lisa Slater Nicholls" <l...@.spacefold.com> wrote:
>> Here's how to do it in T-SQL (you can add this as a little dataset into
>> your
>> report) -- you'll have to adjust the string parsing in the nested select
>> if
>> your server's date representation is not MMDDYYY, but it's got three
>> parts
>> and they are all straightforward. The only exciting bit there is making
>> sure to account for the first month of the year when you put the date
>> together.
>> SELECT CAST(FirstOfLastMonth AS DATETIME) AS FirstOfPreviousMonth,
>> DATEADD(month,1,CAST(FirstOfLastMonth AS DATETIME))-1 AS
>> LastOfPreviousMonth
>> FROM (
>> SELECT FirstOfLastMonth =>> CASE WHEN MONTH(GETDATE()) = 1 THEN '12'
>> ELSE CAST(MONTH(GETDATE()) -1 AS VARCHAR) END +
>> '/1/' +
>> CASE WHEN MONTH(GETDATE()) = 1 THEN CAST(YEAR(GETDATE())-1 AS
>> VARCHAR)
>> ELSE CAST(YEAR(GETDATE()) AS VARCHAR) END) X
>> Here's the trick: get the first day of the last month first (that's the
>> nested select) because you know its day is always day #1 of that month.
>> Once you've done that, getting the *last* day of the previous month is
>> pretty easy, no matter how many days there are in the month! Just use
>> DATEADD to go forward one month from the first day, which you've already
>> derived, and then go back one day <g>. You are probably going to slap
>> yourself on the head when you read that <g>.
>> >L<
>> "Ayman" <ayma...@.gmail.com> wrote in message
>> news:1180453077.604629.38770@.o5g2000hsb.googlegroups.com...
>> >I have to date parameters defaulted as follows:
>> > startdate = dateadd("M",-1,Today())
>> > enddate = today()
>> > So it will display data for the past month from today. Now, what I
>> > need is the following. I need a subscription to be sent out at the
>> > start of the month (so June 1st would be the next one). I need it to
>> > show all data for May (that's 31 days, so far this is doing 30 days).
>> > So using this same logic, it will return May 2nd to May 31st. I need
>> > it to return the first and last of the previous month.
>> > Thanks
> I took a slightly different approach but it worked great. I made two
> data sets with the following code.
> end of month dataset:
> select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))
> start of month dataset:
> select DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0)
> Under Report>Report Parameters I just used the query for the default
> values. Since both scripts only return one value, there is nothing
> else that can be defaulted for either of these parameters. Works
> well, although the first day of the month only puts the date and not
> the time, it does start from midnight though. Thanks for everyone's
> help.
>

Advanced date/time queries

I'm a new member here and love the amount of knowledge available.

1) I have a database schema and it stores repair information for phones, with columns such as ID, Phone manufacturer, model, fault, etc.What query would I have to write to get a query which returns the phone model, its manufacturer (this I know), but also a number of the amount of times that phone model has been raised in the repair table (which is every time a customer hands in a faulty phone). Do I use a count function for the phone model or is there a more efficient way?

2) Another table stores contract information with an expiry date of the contract. How can I write a query that will pick up the contracts due to expire exactly one month for today? I have looked all over the net for some resourced on writing advanced tsql queries, does anyone know of any good websites or even books on the topic?

Thanks

GSS1 wrote:

1) I have a database schema and it stores repair information for phones, with columns such as ID, Phone manufacturer, model, fault, etc.What query would I have to write to get a query which returns the phone model, its manufacturer (this I know), but also a number of the amount of times that phone model has been raised in the repair table (which is every time a customer hands in a faulty phone). Do I use a count function for the phone model or is there a more efficient way?

You could answer this using the query:

Code Snippet

select t.phone_model, t.phone_manuf, count(*) as #faults

from t

group by t.phone_model, t.phone_manuf

GSS1 wrote:

2) Another table stores contract information with an expiry date of the contract. How can I write a query that will pick up the contracts due to expire exactly one month for today? I have looked all over the net for some resourced on writing advanced tsql queries, does anyone know of any good websites or even books on the topic?

This can be done by using query like below:

Code Snippet

select *

from Contracts as c

where c.expiry_date >= dateadd(month, 1, convert(varchar, CURRENT_TIMESTAMP, 112));

The "Inside SQL Server 2005" books are good ones for learning advanced TSQL programming.

ADOMD.net 9.0 (client) and CubeDef.LastProcessed

What is this property (CubeDef.LastProcessed) supposed to return as it does not return the date the cube was last processed?

Does AMO provide this info more reliably ?

TIA
Dave

Hi Dave,

It's a bug - CubeDef.LastProcessed returns the last time the server was restarted as far as I know. See
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124606

...for more details. Take a look at the comment posted by furmangg on April 28th on this entry on my blog for some code which does what you want:
http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!675.entry

It's also available in the Analysis Services Stored Procedure Project here:
http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures&title=CubeInfo

HTH,

Chris

|||

Thanks Chris,

Its not resolved in SP1 either... Im going to check out AMO.

Cheers
Dave

|||FWIW AMO works for AS2005 when retrieving the last processed datetime. Doesnt work for AS2000 or local cubes.

ADOMD.net 9.0 (client) and CubeDef.LastProcessed

What is this property (CubeDef.LastProcessed) supposed to return as it does not return the date the cube was last processed?

Does AMO provide this info more reliably ?

TIA
Dave

Hi Dave,

It's a bug - CubeDef.LastProcessed returns the last time the server was restarted as far as I know. See
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124606

...for more details. Take a look at the comment posted by furmangg on April 28th on this entry on my blog for some code which does what you want:
http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!675.entry

It's also available in the Analysis Services Stored Procedure Project here:
http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures&title=CubeInfo

HTH,

Chris

|||

Thanks Chris,

Its not resolved in SP1 either... Im going to check out AMO.

Cheers
Dave

|||FWIW AMO works for AS2005 when retrieving the last processed datetime. Doesnt work for AS2000 or local cubes.

ADODB DATE RETRIEVE PROBLEM

HI , I HAVE A BIG PROBLEM,
I TRY TO EXPLAIN YOU.
I HAVE MANY TABLE WHOSE CONTAIN DATETIME FIELDS ,
I DISPLAY THIS VALUE ON A ASP PAGE BY USING AN ADODB RECORDSET AND ADODB
COMMAND CALLING A STORED PROCEDURE.
WHEN THIS RECORDSET RETRIEVE DATA THE DATE VALUE IS VERY STRANGE, INFACT IN
THE TABLE BY USING "SQL QUERY ANALIZER I SEE 2004-10-10 14:34:45.613
AND IT'S OK, BUT ADODB RECORDSET RETRIEVE /10/aa 14.34.45, I DON'T KNOW WHY
OR WHAT CAN I DO.
CAN SOMEONE HELP ME?
TAHNK YOU
Gianni
Look at regional settings on your workstation.
Also , have you tried to use FORMAT function on client side to see the date
output in format that you want.
"GIANNI" <GIANNI@.discussions.microsoft.com> wrote in message
news:2A7D3B28-5399-482A-9710-A6DFBF1A0E9B@.microsoft.com...
> HI , I HAVE A BIG PROBLEM,
> I TRY TO EXPLAIN YOU.
> I HAVE MANY TABLE WHOSE CONTAIN DATETIME FIELDS ,
> I DISPLAY THIS VALUE ON A ASP PAGE BY USING AN ADODB RECORDSET AND ADODB
> COMMAND CALLING A STORED PROCEDURE.
> WHEN THIS RECORDSET RETRIEVE DATA THE DATE VALUE IS VERY STRANGE, INFACT
IN
> THE TABLE BY USING "SQL QUERY ANALIZER I SEE 2004-10-10 14:34:45.613
> AND IT'S OK, BUT ADODB RECORDSET RETRIEVE /10/aa 14.34.45, I DON'T KNOW
WHY
> OR WHAT CAN I DO.
> CAN SOMEONE HELP ME?
> TAHNK YOU
>
|||Hi Uri,
the regional settings are ok, infact thi problem appeared when i restored
windows 2000 file system on my server, this application work well for 1 Year.
Regarding the format function, i can't format the value because the
retrivved date is very strange and the function doesn't work.
I have 3 Asp Apllication now that when retrieve date value have this problem.
"Uri Dimant" wrote:

> Gianni
> Look at regional settings on your workstation.
> Also , have you tried to use FORMAT function on client side to see the date
> output in format that you want.
>
> "GIANNI" <GIANNI@.discussions.microsoft.com> wrote in message
> news:2A7D3B28-5399-482A-9710-A6DFBF1A0E9B@.microsoft.com...
> IN
> WHY
>
>
|||Hi
It seems to me that you have a different/version file system applied to the
server. What else?
"GIANNI" <GIANNI@.discussions.microsoft.com> wrote in message
news:F6529C7F-4103-4969-B13A-52CA8763EB8D@.microsoft.com...
> Hi Uri,
> the regional settings are ok, infact thi problem appeared when i restored
> windows 2000 file system on my server, this application work well for 1
Year.
> Regarding the format function, i can't format the value because the
> retrivved date is very strange and the function doesn't work.
> I have 3 Asp Apllication now that when retrieve date value have this
problem.[vbcol=seagreen]
> "Uri Dimant" wrote:
date[vbcol=seagreen]
ADODB[vbcol=seagreen]
INFACT[vbcol=seagreen]
KNOW[vbcol=seagreen]
|||Only this,
but I can't hunderstand if something is happen to the Adodb component,
or now is changed something in IIS , infact if i use ADODB in Visual Basic
and connect it with the same table and fields i haven't this kind of problem.
"GIANNI" wrote:

> HI , I HAVE A BIG PROBLEM,
> I TRY TO EXPLAIN YOU.
> I HAVE MANY TABLE WHOSE CONTAIN DATETIME FIELDS ,
> I DISPLAY THIS VALUE ON A ASP PAGE BY USING AN ADODB RECORDSET AND ADODB
> COMMAND CALLING A STORED PROCEDURE.
> WHEN THIS RECORDSET RETRIEVE DATA THE DATE VALUE IS VERY STRANGE, INFACT IN
> THE TABLE BY USING "SQL QUERY ANALIZER I SEE 2004-10-10 14:34:45.613
> AND IT'S OK, BUT ADODB RECORDSET RETRIEVE /10/aa 14.34.45, I DON'T KNOW WHY
> OR WHAT CAN I DO.
> CAN SOMEONE HELP ME?
> TAHNK YOU
>
|||Only this,
but I can't hunderstand if something is happen to the Adodb component,
or now is changed something in IIS , infact if i use ADODB in Visual Basic
and connect it with the same table and fields i haven't this kind of problem.
"Uri Dimant" wrote:

> Hi
> It seems to me that you have a different/version file system applied to the
> server. What else?
>
> "GIANNI" <GIANNI@.discussions.microsoft.com> wrote in message
> news:F6529C7F-4103-4969-B13A-52CA8763EB8D@.microsoft.com...
> Year.
> problem.
> date
> ADODB
> INFACT
> KNOW
>
>
|||Gianni,
it depends on what you are doing with the recordset/dataset, but you might
want to look at using the CONVERT function on this field/output paramenter
to convert to a varchar. The CONVERT function has an optional 3rd argument
which can be used to format the datetime. On the ASP page you can have a
label explaining the format as it will obviously be invariant on the locale
of the client machine. This way you have no locale issues on the SQL Server
box or the IIS server box.
HTH,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul,
I know that I Have to use the convert function,
the problem for me is that until 2 weeks ago the problem didn't exist on my
Server,
now i have to change all my ASp Applications and apply the convert function.
I hoped to solve the problem server side, and not to change all my Stored
Procedure.
However Thanks
"Paul Ibison" wrote:

> Gianni,
> it depends on what you are doing with the recordset/dataset, but you might
> want to look at using the CONVERT function on this field/output paramenter
> to convert to a varchar. The CONVERT function has an optional 3rd argument
> which can be used to format the datetime. On the ASP page you can have a
> label explaining the format as it will obviously be invariant on the locale
> of the client machine. This way you have no locale issues on the SQL Server
> box or the IIS server box.
> HTH,
> Paul Ibison (SQL Server MVP)
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

ADODB DATE RETRIEVE PROBLEM

HI , I HAVE A BIG PROBLEM,
I TRY TO EXPLAIN YOU.
I HAVE MANY TABLE WHOSE CONTAIN DATETIME FIELDS ,
I DISPLAY THIS VALUE ON A ASP PAGE BY USING AN ADODB RECORDSET AND ADODB
COMMAND CALLING A STORED PROCEDURE.
WHEN THIS RECORDSET RETRIEVE DATA THE DATE VALUE IS VERY STRANGE, INFACT IN
THE TABLE BY USING "SQL QUERY ANALIZER I SEE 2004-10-10 14:34:45.613
AND IT'S OK, BUT ADODB RECORDSET RETRIEVE /10/aa 14.34.45, I DON'T KNOW WHY
OR WHAT CAN I DO.
CAN SOMEONE HELP ME?
TAHNK YOUGianni
Look at regional settings on your workstation.
Also , have you tried to use FORMAT function on client side to see the date
output in format that you want.
"GIANNI" <GIANNI@.discussions.microsoft.com> wrote in message
news:2A7D3B28-5399-482A-9710-A6DFBF1A0E9B@.microsoft.com...
> HI , I HAVE A BIG PROBLEM,
> I TRY TO EXPLAIN YOU.
> I HAVE MANY TABLE WHOSE CONTAIN DATETIME FIELDS ,
> I DISPLAY THIS VALUE ON A ASP PAGE BY USING AN ADODB RECORDSET AND ADODB
> COMMAND CALLING A STORED PROCEDURE.
> WHEN THIS RECORDSET RETRIEVE DATA THE DATE VALUE IS VERY STRANGE, INFACT
IN
> THE TABLE BY USING "SQL QUERY ANALIZER I SEE 2004-10-10 14:34:45.613
> AND IT'S OK, BUT ADODB RECORDSET RETRIEVE /10/aa 14.34.45, I DON'T KNOW
WHY
> OR WHAT CAN I DO.
> CAN SOMEONE HELP ME?
> TAHNK YOU
>|||Hi Uri,
the regional settings are ok, infact thi problem appeared when i restored
windows 2000 file system on my server, this application work well for 1 Year.
Regarding the format function, i can't format the value because the
retrivved date is very strange and the function doesn't work.
I have 3 Asp Apllication now that when retrieve date value have this problem.
"Uri Dimant" wrote:
> Gianni
> Look at regional settings on your workstation.
> Also , have you tried to use FORMAT function on client side to see the date
> output in format that you want.
>
> "GIANNI" <GIANNI@.discussions.microsoft.com> wrote in message
> news:2A7D3B28-5399-482A-9710-A6DFBF1A0E9B@.microsoft.com...
> > HI , I HAVE A BIG PROBLEM,
> > I TRY TO EXPLAIN YOU.
> > I HAVE MANY TABLE WHOSE CONTAIN DATETIME FIELDS ,
> > I DISPLAY THIS VALUE ON A ASP PAGE BY USING AN ADODB RECORDSET AND ADODB
> > COMMAND CALLING A STORED PROCEDURE.
> > WHEN THIS RECORDSET RETRIEVE DATA THE DATE VALUE IS VERY STRANGE, INFACT
> IN
> > THE TABLE BY USING "SQL QUERY ANALIZER I SEE 2004-10-10 14:34:45.613
> > AND IT'S OK, BUT ADODB RECORDSET RETRIEVE /10/aa 14.34.45, I DON'T KNOW
> WHY
> > OR WHAT CAN I DO.
> > CAN SOMEONE HELP ME?
> > TAHNK YOU
> >
>
>|||Hi
It seems to me that you have a different/version file system applied to the
server. What else?
"GIANNI" <GIANNI@.discussions.microsoft.com> wrote in message
news:F6529C7F-4103-4969-B13A-52CA8763EB8D@.microsoft.com...
> Hi Uri,
> the regional settings are ok, infact thi problem appeared when i restored
> windows 2000 file system on my server, this application work well for 1
Year.
> Regarding the format function, i can't format the value because the
> retrivved date is very strange and the function doesn't work.
> I have 3 Asp Apllication now that when retrieve date value have this
problem.
> "Uri Dimant" wrote:
> > Gianni
> > Look at regional settings on your workstation.
> > Also , have you tried to use FORMAT function on client side to see the
date
> > output in format that you want.
> >
> >
> >
> > "GIANNI" <GIANNI@.discussions.microsoft.com> wrote in message
> > news:2A7D3B28-5399-482A-9710-A6DFBF1A0E9B@.microsoft.com...
> > > HI , I HAVE A BIG PROBLEM,
> > > I TRY TO EXPLAIN YOU.
> > > I HAVE MANY TABLE WHOSE CONTAIN DATETIME FIELDS ,
> > > I DISPLAY THIS VALUE ON A ASP PAGE BY USING AN ADODB RECORDSET AND
ADODB
> > > COMMAND CALLING A STORED PROCEDURE.
> > > WHEN THIS RECORDSET RETRIEVE DATA THE DATE VALUE IS VERY STRANGE,
INFACT
> > IN
> > > THE TABLE BY USING "SQL QUERY ANALIZER I SEE 2004-10-10 14:34:45.613
> > > AND IT'S OK, BUT ADODB RECORDSET RETRIEVE /10/aa 14.34.45, I DON'T
KNOW
> > WHY
> > > OR WHAT CAN I DO.
> > > CAN SOMEONE HELP ME?
> > > TAHNK YOU
> > >
> >
> >
> >|||Only this,
but I can't hunderstand if something is happen to the Adodb component,
or now is changed something in IIS , infact if i use ADODB in Visual Basic
and connect it with the same table and fields i haven't this kind of problem.
"GIANNI" wrote:
> HI , I HAVE A BIG PROBLEM,
> I TRY TO EXPLAIN YOU.
> I HAVE MANY TABLE WHOSE CONTAIN DATETIME FIELDS ,
> I DISPLAY THIS VALUE ON A ASP PAGE BY USING AN ADODB RECORDSET AND ADODB
> COMMAND CALLING A STORED PROCEDURE.
> WHEN THIS RECORDSET RETRIEVE DATA THE DATE VALUE IS VERY STRANGE, INFACT IN
> THE TABLE BY USING "SQL QUERY ANALIZER I SEE 2004-10-10 14:34:45.613
> AND IT'S OK, BUT ADODB RECORDSET RETRIEVE /10/aa 14.34.45, I DON'T KNOW WHY
> OR WHAT CAN I DO.
> CAN SOMEONE HELP ME?
> TAHNK YOU
>|||Only this,
but I can't hunderstand if something is happen to the Adodb component,
or now is changed something in IIS , infact if i use ADODB in Visual Basic
and connect it with the same table and fields i haven't this kind of problem.
"Uri Dimant" wrote:
> Hi
> It seems to me that you have a different/version file system applied to the
> server. What else?
>
> "GIANNI" <GIANNI@.discussions.microsoft.com> wrote in message
> news:F6529C7F-4103-4969-B13A-52CA8763EB8D@.microsoft.com...
> > Hi Uri,
> > the regional settings are ok, infact thi problem appeared when i restored
> > windows 2000 file system on my server, this application work well for 1
> Year.
> > Regarding the format function, i can't format the value because the
> > retrivved date is very strange and the function doesn't work.
> > I have 3 Asp Apllication now that when retrieve date value have this
> problem.
> >
> > "Uri Dimant" wrote:
> >
> > > Gianni
> > > Look at regional settings on your workstation.
> > > Also , have you tried to use FORMAT function on client side to see the
> date
> > > output in format that you want.
> > >
> > >
> > >
> > > "GIANNI" <GIANNI@.discussions.microsoft.com> wrote in message
> > > news:2A7D3B28-5399-482A-9710-A6DFBF1A0E9B@.microsoft.com...
> > > > HI , I HAVE A BIG PROBLEM,
> > > > I TRY TO EXPLAIN YOU.
> > > > I HAVE MANY TABLE WHOSE CONTAIN DATETIME FIELDS ,
> > > > I DISPLAY THIS VALUE ON A ASP PAGE BY USING AN ADODB RECORDSET AND
> ADODB
> > > > COMMAND CALLING A STORED PROCEDURE.
> > > > WHEN THIS RECORDSET RETRIEVE DATA THE DATE VALUE IS VERY STRANGE,
> INFACT
> > > IN
> > > > THE TABLE BY USING "SQL QUERY ANALIZER I SEE 2004-10-10 14:34:45.613
> > > > AND IT'S OK, BUT ADODB RECORDSET RETRIEVE /10/aa 14.34.45, I DON'T
> KNOW
> > > WHY
> > > > OR WHAT CAN I DO.
> > > > CAN SOMEONE HELP ME?
> > > > TAHNK YOU
> > > >
> > >
> > >
> > >
>
>|||Gianni,
it depends on what you are doing with the recordset/dataset, but you might
want to look at using the CONVERT function on this field/output paramenter
to convert to a varchar. The CONVERT function has an optional 3rd argument
which can be used to format the datetime. On the ASP page you can have a
label explaining the format as it will obviously be invariant on the locale
of the client machine. This way you have no locale issues on the SQL Server
box or the IIS server box.
HTH,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||Thanks Paul,
I know that I Have to use the convert function,
the problem for me is that until 2 weeks ago the problem didn't exist on my
Server,
now i have to change all my ASp Applications and apply the convert function.
I hoped to solve the problem server side, and not to change all my Stored
Procedure.
However Thanks
"Paul Ibison" wrote:
> Gianni,
> it depends on what you are doing with the recordset/dataset, but you might
> want to look at using the CONVERT function on this field/output paramenter
> to convert to a varchar. The CONVERT function has an optional 3rd argument
> which can be used to format the datetime. On the ASP page you can have a
> label explaining the format as it will obviously be invariant on the locale
> of the client machine. This way you have no locale issues on the SQL Server
> box or the IIS server box.
> HTH,
> Paul Ibison (SQL Server MVP)
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

ADODB DATE RETRIEVE PROBLEM

HI , I HAVE A BIG PROBLEM,
I TRY TO EXPLAIN YOU.
I HAVE MANY TABLE WHOSE CONTAIN DATETIME FIELDS ,
I DISPLAY THIS VALUE ON A ASP PAGE BY USING AN ADODB RECORDSET AND ADODB
COMMAND CALLING A STORED PROCEDURE.
WHEN THIS RECORDSET RETRIEVE DATA THE DATE VALUE IS VERY STRANGE, INFACT IN
THE TABLE BY USING "SQL QUERY ANALIZER I SEE 2004-10-10 14:34:45.613
AND IT'S OK, BUT ADODB RECORDSET RETRIEVE /10/aa 14.34.45, I DON'T KNOW WHY
OR WHAT CAN I DO.
CAN SOMEONE HELP ME?
TAHNK YOUGianni
Look at regional settings on your workstation.
Also , have you tried to use FORMAT function on client side to see the date
output in format that you want.
"GIANNI" <GIANNI@.discussions.microsoft.com> wrote in message
news:2A7D3B28-5399-482A-9710-A6DFBF1A0E9B@.microsoft.com...
> HI , I HAVE A BIG PROBLEM,
> I TRY TO EXPLAIN YOU.
> I HAVE MANY TABLE WHOSE CONTAIN DATETIME FIELDS ,
> I DISPLAY THIS VALUE ON A ASP PAGE BY USING AN ADODB RECORDSET AND ADODB
> COMMAND CALLING A STORED PROCEDURE.
> WHEN THIS RECORDSET RETRIEVE DATA THE DATE VALUE IS VERY STRANGE, INFACT
IN
> THE TABLE BY USING "SQL QUERY ANALIZER I SEE 2004-10-10 14:34:45.613
> AND IT'S OK, BUT ADODB RECORDSET RETRIEVE /10/aa 14.34.45, I DON'T KNOW
WHY
> OR WHAT CAN I DO.
> CAN SOMEONE HELP ME?
> TAHNK YOU
>|||Hi Uri,
the regional settings are ok, infact thi problem appeared when i restored
windows 2000 file system on my server, this application work well for 1 Year
.
Regarding the format function, i can't format the value because the
retrivved date is very strange and the function doesn't work.
I have 3 Asp Apllication now that when retrieve date value have this problem
.
"Uri Dimant" wrote:

> Gianni
> Look at regional settings on your workstation.
> Also , have you tried to use FORMAT function on client side to see the da
te
> output in format that you want.
>
> "GIANNI" <GIANNI@.discussions.microsoft.com> wrote in message
> news:2A7D3B28-5399-482A-9710-A6DFBF1A0E9B@.microsoft.com...
> IN
> WHY
>
>|||Hi
It seems to me that you have a different/version file system applied to the
server. What else?
"GIANNI" <GIANNI@.discussions.microsoft.com> wrote in message
news:F6529C7F-4103-4969-B13A-52CA8763EB8D@.microsoft.com...
> Hi Uri,
> the regional settings are ok, infact thi problem appeared when i restored
> Windows 2000 file system on my server, this application work well for 1
Year.
> Regarding the format function, i can't format the value because the
> retrivved date is very strange and the function doesn't work.
> I have 3 Asp Apllication now that when retrieve date value have this
problem.[vbcol=seagreen]
> "Uri Dimant" wrote:
>
date[vbcol=seagreen]
ADODB[vbcol=seagreen]
INFACT[vbcol=seagreen]
KNOW[vbcol=seagreen]|||Only this,
but I can't hunderstand if something is happen to the Adodb component,
or now is changed something in IIS , infact if i use ADODB in Visual Basic
and connect it with the same table and fields i haven't this kind of problem
.
"GIANNI" wrote:

> HI , I HAVE A BIG PROBLEM,
> I TRY TO EXPLAIN YOU.
> I HAVE MANY TABLE WHOSE CONTAIN DATETIME FIELDS ,
> I DISPLAY THIS VALUE ON A ASP PAGE BY USING AN ADODB RECORDSET AND ADODB
> COMMAND CALLING A STORED PROCEDURE.
> WHEN THIS RECORDSET RETRIEVE DATA THE DATE VALUE IS VERY STRANGE, INFACT I
N
> THE TABLE BY USING "SQL QUERY ANALIZER I SEE 2004-10-10 14:34:45.613
> AND IT'S OK, BUT ADODB RECORDSET RETRIEVE /10/aa 14.34.45, I DON'T KNOW W
HY
> OR WHAT CAN I DO.
> CAN SOMEONE HELP ME?
> TAHNK YOU
>|||Only this,
but I can't hunderstand if something is happen to the Adodb component,
or now is changed something in IIS , infact if i use ADODB in Visual Basic
and connect it with the same table and fields i haven't this kind of problem
.
"Uri Dimant" wrote:

> Hi
> It seems to me that you have a different/version file system applied to th
e
> server. What else?
>
> "GIANNI" <GIANNI@.discussions.microsoft.com> wrote in message
> news:F6529C7F-4103-4969-B13A-52CA8763EB8D@.microsoft.com...
> Year.
> problem.
> date
> ADODB
> INFACT
> KNOW
>
>|||Gianni,
it depends on what you are doing with the recordset/dataset, but you might
want to look at using the CONVERT function on this field/output paramenter
to convert to a varchar. The CONVERT function has an optional 3rd argument
which can be used to format the datetime. On the ASP page you can have a
label explaining the format as it will obviously be invariant on the locale
of the client machine. This way you have no locale issues on the SQL Server
box or the IIS server box.
HTH,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||Thanks Paul,
I know that I Have to use the convert function,
the problem for me is that until 2 weeks ago the problem didn't exist on my
Server,
now i have to change all my ASp Applications and apply the convert function.
I hoped to solve the problem server side, and not to change all my Stored
Procedure.
However Thanks
"Paul Ibison" wrote:

> Gianni,
> it depends on what you are doing with the recordset/dataset, but you might
> want to look at using the CONVERT function on this field/output paramenter
> to convert to a varchar. The CONVERT function has an optional 3rd argument
> which can be used to format the datetime. On the ASP page you can have a
> label explaining the format as it will obviously be invariant on the local
e
> of the client machine. This way you have no locale issues on the SQL Serve
r
> box or the IIS server box.
> HTH,
> Paul Ibison (SQL Server MVP)
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>