regarding datetime in asp.net

hi ,

i had textbox which is used to enter datetime . in the database, the data type is date/time. now my requirement is when the textbox is empty. i want 2 store a default value. which value is suitable for datetime.

i had given as follows

if textbox12.text = "" then

textbox12.text = 5/5/2000 ---- it is storing this value.

end if

but if i want 2 update this date means it is not supporting . if i select current date using calender it was saved as 12/30/1899.

can anyone help me.

regards

m.arunachalam

You can try
textbox12.Text = DateTime.Today

Reformatting Parameters before using them in Sql query

Hello,
We use Reporting Services to do reports from a (rather old iSeries /
AS400) database. Date values are stored as numeric data in the format
YYYYMMDD, rather than with a proper "Date" type. This is quite common in
older host-based databases.
In one of our reports, the user should be able to select data by
entering date-from and date-to into parameters, before starting the report.
All that we are able to do right now is to let the user enter the data
in the original YYYYMMDD format, in order to be able to use it in a
parametrized query. What I want to do, is:
a) Let the user enter a date as a parameter in a proper Date format
b) Before executing the query, convert this parameter to YYYYMMDD number
format
I guess this conversion should be done in a custom assembly. However,
there doesn't seem to be a "plug" where I could attach this conversion
code. The only thing I found is to use Generic Query - but this would
really be last resort, because I think it is very ugly and not really
RAD, to lose all of the graphic helpers just because of the date
conversions.
Any help would be greatly appreciated.
Ursuse the following custom code:
Public Function ConvertToYYYYMMDD(pYourDateFormat as Date) as String
ConvertToYYYYMMDD = Format(pYourDateFormat, "yyyyMMdd")
End Function 'ConvertToYYYYMMDD
Then in your query you would use for your parameter portion:
code.ConvertToYYYYMMDD(Parameters!YourDateParameter.Value)
"Urs Eichmann" <xx@.yy.ch> wrote in message
news:eRjnk4%23nEHA.324@.TK2MSFTNGP11.phx.gbl...
> Hello,
> We use Reporting Services to do reports from a (rather old iSeries /
> AS400) database. Date values are stored as numeric data in the format
> YYYYMMDD, rather than with a proper "Date" type. This is quite common in
> older host-based databases.
> In one of our reports, the user should be able to select data by
> entering date-from and date-to into parameters, before starting the
report.
> All that we are able to do right now is to let the user enter the data
> in the original YYYYMMDD format, in order to be able to use it in a
> parametrized query. What I want to do, is:
> a) Let the user enter a date as a parameter in a proper Date format
> b) Before executing the query, convert this parameter to YYYYMMDD number
> format
> I guess this conversion should be done in a custom assembly. However,
> there doesn't seem to be a "plug" where I could attach this conversion
> code. The only thing I found is to use Generic Query - but this would
> really be last resort, because I think it is very ugly and not really
> RAD, to lose all of the graphic helpers just because of the date
> conversions.
> Any help would be greatly appreciated.
> Urs|||Thanks mike, but AFAIK I can only insert "code.convertto..." into my
query if I don't use the Graphic Query designer and instead use the
Generic Query Designer, which I don't want to (see my first message).
Urs
mike wrote:
> use the following custom code:
> Public Function ConvertToYYYYMMDD(pYourDateFormat as Date) as String
> ConvertToYYYYMMDD = Format(pYourDateFormat, "yyyyMMdd")
> End Function 'ConvertToYYYYMMDD
> Then in your query you would use for your parameter portion:
> code.ConvertToYYYYMMDD(Parameters!YourDateParameter.Value)
>
> "Urs Eichmann" <xx@.yy.ch> wrote in message
> news:eRjnk4%23nEHA.324@.TK2MSFTNGP11.phx.gbl...
>>Hello,
>>We use Reporting Services to do reports from a (rather old iSeries /
>>AS400) database. Date values are stored as numeric data in the format
>>YYYYMMDD, rather than with a proper "Date" type. This is quite common in
>>older host-based databases.
>>In one of our reports, the user should be able to select data by
>>entering date-from and date-to into parameters, before starting the
> report.
>>All that we are able to do right now is to let the user enter the data
>>in the original YYYYMMDD format, in order to be able to use it in a
>>parametrized query. What I want to do, is:
>>a) Let the user enter a date as a parameter in a proper Date format
>>b) Before executing the query, convert this parameter to YYYYMMDD number
>>format
>>I guess this conversion should be done in a custom assembly. However,
>>there doesn't seem to be a "plug" where I could attach this conversion
>>code. The only thing I found is to use Generic Query - but this would
>>really be last resort, because I think it is very ugly and not really
>>RAD, to lose all of the graphic helpers just because of the date
>>conversions.
>>Any help would be greatly appreciated.
>>Urs
>
>|||Urs,
Did you ever find a work around. I am in the same boat.
Thanks, Eric
"Urs Eichmann" wrote:
> Thanks mike, but AFAIK I can only insert "code.convertto..." into my
> query if I don't use the Graphic Query designer and instead use the
> Generic Query Designer, which I don't want to (see my first message).
> Urs
>
> mike wrote:
> > use the following custom code:
> >
> > Public Function ConvertToYYYYMMDD(pYourDateFormat as Date) as String
> > ConvertToYYYYMMDD = Format(pYourDateFormat, "yyyyMMdd")
> > End Function 'ConvertToYYYYMMDD
> >
> > Then in your query you would use for your parameter portion:
> > code.ConvertToYYYYMMDD(Parameters!YourDateParameter.Value)
> >
> >
> >
> > "Urs Eichmann" <xx@.yy.ch> wrote in message
> > news:eRjnk4%23nEHA.324@.TK2MSFTNGP11.phx.gbl...
> >
> >>Hello,
> >>We use Reporting Services to do reports from a (rather old iSeries /
> >>AS400) database. Date values are stored as numeric data in the format
> >>YYYYMMDD, rather than with a proper "Date" type. This is quite common in
> >>older host-based databases.
> >>
> >>In one of our reports, the user should be able to select data by
> >>entering date-from and date-to into parameters, before starting the
> >
> > report.
> >
> >>All that we are able to do right now is to let the user enter the data
> >>in the original YYYYMMDD format, in order to be able to use it in a
> >>parametrized query. What I want to do, is:
> >>
> >>a) Let the user enter a date as a parameter in a proper Date format
> >>b) Before executing the query, convert this parameter to YYYYMMDD number
> >>format
> >>
> >>I guess this conversion should be done in a custom assembly. However,
> >>there doesn't seem to be a "plug" where I could attach this conversion
> >>code. The only thing I found is to use Generic Query - but this would
> >>really be last resort, because I think it is very ugly and not really
> >>RAD, to lose all of the graphic helpers just because of the date
> >>conversions.
> >>
> >>Any help would be greatly appreciated.
> >>
> >>Urs
> >
> >
> >
>sql

reformatting DATE

I currently have a date column that draws from the now() function in my
asp script. The date looks like this:

2/5/2004 10:09:52 AM

As you can see, there is the day and the time data all in the same
string.

What I would like to do is run a group by clause that groups
all the dates that are on the same day togeather. But how do I seperate
this data in SQL? I know there are VB functions that can do this, but in
order for the group function to work when running a sql statement, I
have to do it in SQL itself. Does anyone know how this is done?

Thank you!

Bill

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Bill" <BillZimmerman@.gospellight.com> wrote in message
news:4023cc79$0$196$75868355@.news.frii.net...
> I currently have a date column that draws from the now() function in my
> asp script. The date looks like this:
> 2/5/2004 10:09:52 AM
> As you can see, there is the day and the time data all in the same
> string.
> What I would like to do is run a group by clause that groups
> all the dates that are on the same day togeather. But how do I seperate
> this data in SQL? I know there are VB functions that can do this, but in
> order for the group function to work when running a sql statement, I
> have to do it in SQL itself. Does anyone know how this is done?
> Thank you!
> Bill
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

I'm not completely sure what you're looking for, but perhaps something like
this?

select convert(char(8), DateColumn, 112) as 'Day', sum(SomeColumn) as
'Total'
from dbo.MyTable
group by convert(char(8), DateColumn, 112)

Simon

reference member in user hierarchy

hi,

I have a user hierarchy in my Date Dimension such as [Calendar]->[Years]->[Quarters]

And I can access the member by directly call the memeber name, for example:

select [Date].[Calendar].[Years].[2006].[Q2] on columns, ...................................

The results will be correct. However, if I use reference to call the member, for example:

select [Date].[Calendar].[Years].[2006].&[2] on columns, ...................................

There will be nothing in my results.

Would you please tell me what shall I do to enable the reference call to my hierarchy member? Thanks.

With "select [Date].[Calendar].[Years].[2006].&[2] on columns, you are referencing the keys for the member.

"[Date].[Calendar].[Years].&[2006].&[2]" might help.

Another method is [Date].[Calendar].[Years].Lastchild.Lastchild. With this method you will always find the last quarter of the last year in your time dimension.

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your answer. I have also tried "[Date].[Calendar].[Years].&[2006].&[2]" but no use. I still get nothing from that.

I was forced to use the format: "[Date].[Calendar].[Years].&[2006].&[2]" because this is a fixed format in a third-part software. I guess maybe there is sth wrong about the settings of my user hierarchy?

|||

It can be a problem with key for the Quarter member. Are you using integers like 1,2,3,4 for Quarters? In that case you can make a combination by year and quarter for the quarter key. Check also your attribute relation between quarter and year in the dimension editor.

Regards

Thomas

|||

Hi, Thomas,

Thanks and would you please give me a little bit more details?

Do you mean that I should use 1,2,3,4 as the quarter key for Quarters?

And do you mean that I should build the attribute relation between quarter and year? I have tried both with/without attribute relation but it did not work. Thx.

|||

No. You should use a composite key with both the year and the quarter. A quarter key like 1,2,3,4 is not unique. If you klick on the key-column, for the level, in the properties(dimension editor) you can add year to quarter(in the key) and create a collection. This is in the dataitem collection editor.

Regards

Thomas Ivarsson

|||

hi, Thomas,

Thanks and I have tried to add a composite key (year+quarter) according to your steps.

But during processing, there is an error message: The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute.

Would you please tell me how to deal with this problem? Thanks a lot.

|||

Hello. Make a new column, for either year or quarter, and use the TSQL convert och cast-function to change the data type to something common.

You can also simply use the datepart function for year and quarter and return two integer fields.

Regards

Thomas Ivarsson

|||

hi, Thomas

I have checked that in the AS2005, my Year and Quarter are in the format: Integar.

And in the data source view, I see that the source columns of the key column are both: System.Int32

However, the error still exists, do you have any idea about this? Thanks.

|||

BTW, do I also need to change the Name Column (or the value column) to the integer column I defined in the source?

|||

hi, Thomas,

I have finally get the result I want ([Date].[Calendar].&[2005].&[2] is available).

And the step follows: 1. Remove any attribute relationship in dimension date. 2. Build a new integer column (1,2,3,4) as the source of the key column for Quarter. 3. Specify the key column of Quarter to that new integer column.

I do not know if this will cause any other problems. Anyway, thanks a lot for your help.

|||

I realized this now. So you have a join on one of the levels in your time hierarchy above the leaf level? In the dimension usage tab for the cube you must use the same combination as in your composite key. You do not have to change the name column.

Check the Adventure Works Demo project that is part of the installation. Have a look at the date.dim there(and the attribute month_name(properties)) and check the relation between the date dim and sales targets measure group in the Adventure Works cube.

Regards

Thomas Ivarsson

|||

Nice that it works. Without attribute relations you will not get any aggregations on the time dimension. Another problem is that your calculations can be wrong. What you needed to do was to add 2006(Year) to the quarters(1,2,3,4) so that their collection will point to year and quarter, at the same time. Actually you combine each year with each of their quarter in this way. Quarter(1,2,3,4) will not point to a specific year if you do not do this(ie add the year key to each quarter key).

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your response! I have several questions about your answer.

First, what do you mean by have a join on one of the levles above the leaf levels? And how can I see that?

Second, I have seen the AdventureWorks cube and see the attribute month_name has a combination key column of year + month. But in the dim usage table, I do not know how to modify my own project. I can see a RED line under my date dimension and I think that indicates error. However, if I add an attribute relationship between Quarter and Year, the dim usage table will automaticlly add this relationship to itself. (In other words, I can not find the place to add sth about the combination key in the dim usage table)

|||

Hello again Jeremy. If you scroll out to the right, in the dimension usage tab of the cube editor(In the Adventure Works demo cube), you will find a box where the dimension date intersect with the measure group Sales Targets. All the other relations between date and the measure groups in this cube are at the lowest(leaf level) or the box empty(no relation). Click the box(date dim and Sales Targets) There you can see the granularity attribute for the relation, thats the level that is used in the join between the measure group and the dimension.

Check here that you join on the correct column to avoid the error message you talked about before: "The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute." You will only have to this if you have changed the key column for the Quarter member in your time dimension.

Regards

Thomas Ivarsson

reference member in user hierarchy

hi,

I have a user hierarchy in my Date Dimension such as [Calendar]->[Years]->[Quarters]

And I can access the member by directly call the memeber name, for example:

select [Date].[Calendar].[Years].[2006].[Q2] on columns, ...................................

The results will be correct. However, if I use reference to call the member, for example:

select [Date].[Calendar].[Years].[2006].&[2] on columns, ...................................

There will be nothing in my results.

Would you please tell me what shall I do to enable the reference call to my hierarchy member? Thanks.

With "select [Date].[Calendar].[Years].[2006].&[2] on columns, you are referencing the keys for the member.

"[Date].[Calendar].[Years].&[2006].&[2]" might help.

Another method is [Date].[Calendar].[Years].Lastchild.Lastchild. With this method you will always find the last quarter of the last year in your time dimension.

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your answer. I have also tried "[Date].[Calendar].[Years].&[2006].&[2]" but no use. I still get nothing from that.

I was forced to use the format: "[Date].[Calendar].[Years].&[2006].&[2]" because this is a fixed format in a third-part software. I guess maybe there is sth wrong about the settings of my user hierarchy?

|||

It can be a problem with key for the Quarter member. Are you using integers like 1,2,3,4 for Quarters? In that case you can make a combination by year and quarter for the quarter key. Check also your attribute relation between quarter and year in the dimension editor.

Regards

Thomas

|||

Hi, Thomas,

Thanks and would you please give me a little bit more details?

Do you mean that I should use 1,2,3,4 as the quarter key for Quarters?

And do you mean that I should build the attribute relation between quarter and year? I have tried both with/without attribute relation but it did not work. Thx.

|||

No. You should use a composite key with both the year and the quarter. A quarter key like 1,2,3,4 is not unique. If you klick on the key-column, for the level, in the properties(dimension editor) you can add year to quarter(in the key) and create a collection. This is in the dataitem collection editor.

Regards

Thomas Ivarsson

|||

hi, Thomas,

Thanks and I have tried to add a composite key (year+quarter) according to your steps.

But during processing, there is an error message: The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute.

Would you please tell me how to deal with this problem? Thanks a lot.

|||

Hello. Make a new column, for either year or quarter, and use the TSQL convert och cast-function to change the data type to something common.

You can also simply use the datepart function for year and quarter and return two integer fields.

Regards

Thomas Ivarsson

|||

hi, Thomas

I have checked that in the AS2005, my Year and Quarter are in the format: Integar.

And in the data source view, I see that the source columns of the key column are both: System.Int32

However, the error still exists, do you have any idea about this? Thanks.

|||

BTW, do I also need to change the Name Column (or the value column) to the integer column I defined in the source?

|||

hi, Thomas,

I have finally get the result I want ([Date].[Calendar].&[2005].&[2] is available).

And the step follows: 1. Remove any attribute relationship in dimension date. 2. Build a new integer column (1,2,3,4) as the source of the key column for Quarter. 3. Specify the key column of Quarter to that new integer column.

I do not know if this will cause any other problems. Anyway, thanks a lot for your help.

|||

I realized this now. So you have a join on one of the levels in your time hierarchy above the leaf level? In the dimension usage tab for the cube you must use the same combination as in your composite key. You do not have to change the name column.

Check the Adventure Works Demo project that is part of the installation. Have a look at the date.dim there(and the attribute month_name(properties)) and check the relation between the date dim and sales targets measure group in the Adventure Works cube.

Regards

Thomas Ivarsson

|||

Nice that it works. Without attribute relations you will not get any aggregations on the time dimension. Another problem is that your calculations can be wrong. What you needed to do was to add 2006(Year) to the quarters(1,2,3,4) so that their collection will point to year and quarter, at the same time. Actually you combine each year with each of their quarter in this way. Quarter(1,2,3,4) will not point to a specific year if you do not do this(ie add the year key to each quarter key).

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your response! I have several questions about your answer.

First, what do you mean by have a join on one of the levles above the leaf levels? And how can I see that?

Second, I have seen the AdventureWorks cube and see the attribute month_name has a combination key column of year + month. But in the dim usage table, I do not know how to modify my own project. I can see a RED line under my date dimension and I think that indicates error. However, if I add an attribute relationship between Quarter and Year, the dim usage table will automaticlly add this relationship to itself. (In other words, I can not find the place to add sth about the combination key in the dim usage table)

|||

Hello again Jeremy. If you scroll out to the right, in the dimension usage tab of the cube editor(In the Adventure Works demo cube), you will find a box where the dimension date intersect with the measure group Sales Targets. All the other relations between date and the measure groups in this cube are at the lowest(leaf level) or the box empty(no relation). Click the box(date dim and Sales Targets) There you can see the granularity attribute for the relation, thats the level that is used in the join between the measure group and the dimension.

Check here that you join on the correct column to avoid the error message you talked about before: "The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute." You will only have to this if you have changed the key column for the Quarter member in your time dimension.

Regards

Thomas Ivarsson

reference member in user hierarchy

hi,

I have a user hierarchy in my Date Dimension such as [Calendar]->[Years]->[Quarters]

And I can access the member by directly call the memeber name, for example:

select [Date].[Calendar].[Years].[2006].[Q2] on columns, ...................................

The results will be correct. However, if I use reference to call the member, for example:

select [Date].[Calendar].[Years].[2006].&[2] on columns, ...................................

There will be nothing in my results.

Would you please tell me what shall I do to enable the reference call to my hierarchy member? Thanks.

With "select [Date].[Calendar].[Years].[2006].&[2] on columns, you are referencing the keys for the member.

"[Date].[Calendar].[Years].&[2006].&[2]" might help.

Another method is [Date].[Calendar].[Years].Lastchild.Lastchild. With this method you will always find the last quarter of the last year in your time dimension.

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your answer. I have also tried "[Date].[Calendar].[Years].&[2006].&[2]" but no use. I still get nothing from that.

I was forced to use the format: "[Date].[Calendar].[Years].&[2006].&[2]" because this is a fixed format in a third-part software. I guess maybe there is sth wrong about the settings of my user hierarchy?

|||

It can be a problem with key for the Quarter member. Are you using integers like 1,2,3,4 for Quarters? In that case you can make a combination by year and quarter for the quarter key. Check also your attribute relation between quarter and year in the dimension editor.

Regards

Thomas

|||

Hi, Thomas,

Thanks and would you please give me a little bit more details?

Do you mean that I should use 1,2,3,4 as the quarter key for Quarters?

And do you mean that I should build the attribute relation between quarter and year? I have tried both with/without attribute relation but it did not work. Thx.

|||

No. You should use a composite key with both the year and the quarter. A quarter key like 1,2,3,4 is not unique. If you klick on the key-column, for the level, in the properties(dimension editor) you can add year to quarter(in the key) and create a collection. This is in the dataitem collection editor.

Regards

Thomas Ivarsson

|||

hi, Thomas,

Thanks and I have tried to add a composite key (year+quarter) according to your steps.

But during processing, there is an error message: The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute.

Would you please tell me how to deal with this problem? Thanks a lot.

|||

Hello. Make a new column, for either year or quarter, and use the TSQL convert och cast-function to change the data type to something common.

You can also simply use the datepart function for year and quarter and return two integer fields.

Regards

Thomas Ivarsson

|||

hi, Thomas

I have checked that in the AS2005, my Year and Quarter are in the format: Integar.

And in the data source view, I see that the source columns of the key column are both: System.Int32

However, the error still exists, do you have any idea about this? Thanks.

|||

BTW, do I also need to change the Name Column (or the value column) to the integer column I defined in the source?

|||

hi, Thomas,

I have finally get the result I want ([Date].[Calendar].&[2005].&[2] is available).

And the step follows: 1. Remove any attribute relationship in dimension date. 2. Build a new integer column (1,2,3,4) as the source of the key column for Quarter. 3. Specify the key column of Quarter to that new integer column.

I do not know if this will cause any other problems. Anyway, thanks a lot for your help.

|||

I realized this now. So you have a join on one of the levels in your time hierarchy above the leaf level? In the dimension usage tab for the cube you must use the same combination as in your composite key. You do not have to change the name column.

Check the Adventure Works Demo project that is part of the installation. Have a look at the date.dim there(and the attribute month_name(properties)) and check the relation between the date dim and sales targets measure group in the Adventure Works cube.

Regards

Thomas Ivarsson

|||

Nice that it works. Without attribute relations you will not get any aggregations on the time dimension. Another problem is that your calculations can be wrong. What you needed to do was to add 2006(Year) to the quarters(1,2,3,4) so that their collection will point to year and quarter, at the same time. Actually you combine each year with each of their quarter in this way. Quarter(1,2,3,4) will not point to a specific year if you do not do this(ie add the year key to each quarter key).

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your response! I have several questions about your answer.

First, what do you mean by have a join on one of the levles above the leaf levels? And how can I see that?

Second, I have seen the AdventureWorks cube and see the attribute month_name has a combination key column of year + month. But in the dim usage table, I do not know how to modify my own project. I can see a RED line under my date dimension and I think that indicates error. However, if I add an attribute relationship between Quarter and Year, the dim usage table will automaticlly add this relationship to itself. (In other words, I can not find the place to add sth about the combination key in the dim usage table)

|||

Hello again Jeremy. If you scroll out to the right, in the dimension usage tab of the cube editor(In the Adventure Works demo cube), you will find a box where the dimension date intersect with the measure group Sales Targets. All the other relations between date and the measure groups in this cube are at the lowest(leaf level) or the box empty(no relation). Click the box(date dim and Sales Targets) There you can see the granularity attribute for the relation, thats the level that is used in the join between the measure group and the dimension.

Check here that you join on the correct column to avoid the error message you talked about before: "The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute." You will only have to this if you have changed the key column for the Quarter member in your time dimension.

Regards

Thomas Ivarsson

Reference Excel for date?

I have the following query;

SELECT TOP 1 DateTime, TagName, Value, CONVERT(varchar(15), DateTime, 108) AS Time
FROM v_AnalogHistory
WHERE (DateTime1 >= CAST(CONVERT(VARCHAR(8), GETDATE() - 4, 112) AS DATETIME)) AND (DateTime2 < CAST(CONVERT(VARCHAR(8), GETDATE()-3, 112)
AS DATETIME)) AND (TagName = N'AIT-500_TOTAL_NH2CL') AND (wwRetrievalMode = N'delta') AND (CONVERT(decimal(38, 3), Value) IS NOT NULL)
ORDER BY CONVERT(decimal(38, 3), Value), CONVERT(varchar(15), DateTime, 108)

what I would like to do is reference 2 cells in Excel with dates if I need to run this manually. How would I change the sql to do this. Thanks

DateTime1 = cell(1,1)

DateTime2 = cell(2,1)

Something like this:

Code Snippet

declare @.DateTime1 datetime,

@.DateTime2 datetime

select @.DateTime1 = F1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;DATABASE=c:\Book1.xls;HDR=NO', 'Select * from [Sheet1$A1:A1]')

select @.DateTime2 = F1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;DATABASE=c:\Book1.xls;HDR=NO', 'Select * from [Sheet1$A2:A2]')

SELECT TOP 1 DateTime, TagName, Value, CONVERT(varchar(15), DateTime, 108) AS Time

FROM v_AnalogHistory

WHERE (@.DateTime1 >= CAST(CONVERT(VARCHAR(8), GETDATE() - 4, 112) AS DATETIME))

AND (@.DateTime2 < CAST(CONVERT(VARCHAR(8), GETDATE()-3, 112) AS DATETIME))

AND (TagName = N'AIT-500_TOTAL_NH2CL') AND (wwRetrievalMode = N'delta')

AND (CONVERT(decimal(38, 3), Value) IS NOT NULL)

ORDER BY CONVERT(decimal(38, 3), Value), CONVERT(varchar(15), DateTime, 108)

You'll need to ensure that 'Ad Hoc Remote Queries' is enabled on your instance to allow OPENROWSET to function.

|||

Expanding upon Dale's suggestion, if you want to be able to run the code as it is, or on occasion, manually, then it would be better to create a Stored Procedure and call the Stored Procedure. (NOT tested and not asserted to be accurate or runnable -presented for ideas only.)

This procedure can be called in this fashion:

EXECUTE PROCEDURE dbo.MyProcedure

In this case it will attempt to find date values in the Excel File and use them.

If the cells are empty, then the procedure will use the columns DateTime1 and DateTime2.

(From the original posting, I'm assuming that there are such columns...)

EXECUTE PROCEDURE dbo.MyProcedure '2007/06/01', '2007/07/01'

In this case, the procedure will use the dates supplied.

Code Snippet

CREATE PROCEDURE dbo.MyProcedure

( @.InDate1 datetime = NULL,

@.InDate2 datetime = NULL

)

AS

BEGIN

DECLARE

@.ExDate1 datetime,

@.ExDate2 datetime

SELECT @.ExDate1 = F1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;DATABASE=c:\Book1.xls;HDR=NO', 'Select * from [Sheet1$A1:A1]')

SELECT @.ExDate2 = F1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;DATABASE=c:\Book1.xls;HDR=NO', 'Select * from [Sheet1$A2:A2]')

SELECT TOP 1

DateTime,

TagName,

[Value],

Time = convert( varchar(15), DateTime, 108 )

FROM v_AnalogHistory

WHERE ( coalesce( InDate1, nullif( ExDate1, '' ), DateTime1 ) >=

cast( convert( varchar(8), getdate() -4, 112) AS datetime )

AND coalesce( InDate2, nullif( ExDate2, '' ), DateTime2 ) <

cast( convert( varchar(8), getdate() -3, 112) AS datetime )

AND TagName = N'AIT-500_TOTAL_NH2CL'

AND wwRetrievalMode = N'delta'

AND convert( decimal(38,3), [Value] ) IS NOT NULL

)

ORDER BY

convert( decimal(38,3), [Value] ),

convert( varchar(15), DateTime, 108 )

END

GO