DATE RANGE ISSUE

Hi All,
I have below table structure:
ItemID Price FromDate
ToDate
----
--
1 10.00 01/01/2005
12/31/2005
1 9.00 01/01/2004
12/31/2004
2 2.00 01/01/1900
01/01/9999
----
--
Now I need to write a query to get price for all the items for the date
range 01/01/2005 to 12/31/2005
If I use FromDate >= 01/01/2005 and ToDate <= 01/01/2005 then I will get
only one record (ItemID=1). But ItemID =2 also lies in the same daterange,
ie, daterange specified lies between 1900 and 9999. But the query will not
give that result.
Please let me know how to get this record also.
Thanks in advance"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:628A35F2-9F1E-4E5A-B095-E5A45470CC84@.microsoft.com...
> Hi All,
> I have below table structure:
> ItemID Price FromDate
> ToDate
> ----
--
> 1 10.00 01/01/2005
> 12/31/2005
> 1 9.00 01/01/2004
> 12/31/2004
> 2 2.00 01/01/1900
> 01/01/9999
> ----
--
> Now I need to write a query to get price for all the items for the date
> range 01/01/2005 to 12/31/2005
>
> If I use FromDate >= 01/01/2005 and ToDate <= 01/01/2005 then I will get
> only one record (ItemID=1). But ItemID =2 also lies in the same daterange,
> ie, daterange specified lies between 1900 and 9999. But the query will not
> give that result.
>
The only records your query should return are ones with a date of
01/01/2005. I'm not sure how it's returning ItemID 1
ItemID=2 does not lay within that same daterange.
It's fromdate 01/01/1900 is less than 01/01/2005 and it's Todate, is greater
than 01/01/2005.
With the AND, you're asking for stuff BETWEEN 01/01/2005 and 01/01/2005.
ItemID=2 exceeds this range on both ends.
I suspect you want OR?

> Please let me know how to get this record also.
> Thanks in advance
>
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||On Mar 14, 9:49 am, Ram <R...@.discussions.microsoft.com> wrote:
> Hi All,
> I have below table structure:
> ItemID Price FromDate =
=20
> ToDate
> ----=
--=AD--
> 1 10.00 01/01/2005 =
=20
> 12/31/2005
> 1 9.00 01/01/2004 =
=20
> 12/31/2004
> 2 2.00 01/01/1900 =
=20
> 01/01/9999
> ----=
--=AD--
> Now I need to write a query to get price for all the items for the date
> range 01/01/2005 to 12/31/2005
> If I use FromDate >=3D 01/01/2005 and ToDate <=3D 01/01/2005 then I will =
get
> only one record (ItemID=3D1). But ItemID =3D2 also lies in the same dater=
ange,
> ie, daterange specified lies between 1900 and 9999. But the query will not
> give that result.
> Please let me know how to get this record also.
> Thanks in advance
I think you are storing dummy values in fromdate and todate column
when it is unknown (NULL)
If that is the case
(FromDate >=3D 01/01/2005 and ToDate < '01/01/2006' )
OR
FromDate >=3D 01/01/1900 and ToDate <=3D '01/01/9999' )
Note: < 01/01/2006 instead of <=3D 12/31/2005' to take care of time
portion
M A Srinivassql

DATE RANGE ISSUE

Hi All,
I have below table structure:
ItemID Price FromDate
ToDate
-----
1 10.00 01/01/2005
12/31/2005
1 9.00 01/01/2004
12/31/2004
2 2.00 01/01/1900
01/01/9999
-----
Now I need to write a query to get price for all the items for the date
range 01/01/2005 to 12/31/2005
If I use FromDate >= 01/01/2005 and ToDate <= 01/01/2005 then I will get
only one record (ItemID=1). But ItemID =2 also lies in the same daterange,
ie, daterange specified lies between 1900 and 9999. But the query will not
give that result.
Please let me know how to get this record also.
Thanks in advance
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:628A35F2-9F1E-4E5A-B095-E5A45470CC84@.microsoft.com...
> Hi All,
> I have below table structure:
> ItemID Price FromDate
> ToDate
> -----
> 1 10.00 01/01/2005
> 12/31/2005
> 1 9.00 01/01/2004
> 12/31/2004
> 2 2.00 01/01/1900
> 01/01/9999
> -----
> Now I need to write a query to get price for all the items for the date
> range 01/01/2005 to 12/31/2005
>
> If I use FromDate >= 01/01/2005 and ToDate <= 01/01/2005 then I will get
> only one record (ItemID=1). But ItemID =2 also lies in the same daterange,
> ie, daterange specified lies between 1900 and 9999. But the query will not
> give that result.
>
The only records your query should return are ones with a date of
01/01/2005. I'm not sure how it's returning ItemID 1
ItemID=2 does not lay within that same daterange.
It's fromdate 01/01/1900 is less than 01/01/2005 and it's Todate, is greater
than 01/01/2005.
With the AND, you're asking for stuff BETWEEN 01/01/2005 and 01/01/2005.
ItemID=2 exceeds this range on both ends.
I suspect you want OR?

> Please let me know how to get this record also.
> Thanks in advance
>
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
|||On Mar 14, 9:49 am, Ram <R...@.discussions.microsoft.com> wrote:
> Hi All,
> I have below table structure:
> ItemID Price FromDate
> ToDate
> ----X--
> 1 10.00 01/01/2005
> 12/31/2005
> 1 9.00 01/01/2004
> 12/31/2004
> 2 2.00 01/01/1900
> 01/01/9999
> ----X--
> Now I need to write a query to get price for all the items for the date
> range 01/01/2005 to 12/31/2005
> If I use FromDate >= 01/01/2005 and ToDate <= 01/01/2005 then I will get
> only one record (ItemID=1). But ItemID =2 also lies in the same daterange,
> ie, daterange specified lies between 1900 and 9999. But the query will not
> give that result.
> Please let me know how to get this record also.
> Thanks in advance
I think you are storing dummy values in fromdate and todate column
when it is unknown (NULL)
If that is the case
(FromDate >= 01/01/2005 and ToDate < '01/01/2006' )
OR
FromDate >= 01/01/1900 and ToDate <= '01/01/9999' )
Note: < 01/01/2006 instead of <= 12/31/2005' to take care of time
portion
M A Srinivas

DATE RANGE ISSUE

Hi All,
I have below table structure:
ItemID Price FromDate
ToDate
-----
1 10.00 01/01/2005
12/31/2005
1 9.00 01/01/2004
12/31/2004
2 2.00 01/01/1900
01/01/9999
-----
Now I need to write a query to get price for all the items for the date
range 01/01/2005 to 12/31/2005
If I use FromDate >= 01/01/2005 and ToDate <= 01/01/2005 then I will get
only one record (ItemID=1). But ItemID =2 also lies in the same daterange,
ie, daterange specified lies between 1900 and 9999. But the query will not
give that result.
Please let me know how to get this record also.
Thanks in advance"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:628A35F2-9F1E-4E5A-B095-E5A45470CC84@.microsoft.com...
> Hi All,
> I have below table structure:
> ItemID Price FromDate
> ToDate
> -----
> 1 10.00 01/01/2005
> 12/31/2005
> 1 9.00 01/01/2004
> 12/31/2004
> 2 2.00 01/01/1900
> 01/01/9999
> -----
> Now I need to write a query to get price for all the items for the date
> range 01/01/2005 to 12/31/2005
>
> If I use FromDate >= 01/01/2005 and ToDate <= 01/01/2005 then I will get
> only one record (ItemID=1). But ItemID =2 also lies in the same daterange,
> ie, daterange specified lies between 1900 and 9999. But the query will not
> give that result.
>
The only records your query should return are ones with a date of
01/01/2005. I'm not sure how it's returning ItemID 1
ItemID=2 does not lay within that same daterange.
It's fromdate 01/01/1900 is less than 01/01/2005 and it's Todate, is greater
than 01/01/2005.
With the AND, you're asking for stuff BETWEEN 01/01/2005 and 01/01/2005.
ItemID=2 exceeds this range on both ends.
I suspect you want OR?
> Please let me know how to get this record also.
> Thanks in advance
>
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||On Mar 14, 9:49 am, Ram <R...@.discussions.microsoft.com> wrote:
> Hi All,
> I have below table structure:
> ItemID Price FromDate = > ToDate
> ----=--=AD--
> 1 10.00 01/01/2005 = > 12/31/2005
> 1 9.00 01/01/2004 = > 12/31/2004
> 2 2.00 01/01/1900 = > 01/01/9999
> ----=--=AD--
> Now I need to write a query to get price for all the items for the date
> range 01/01/2005 to 12/31/2005
> If I use FromDate >=3D 01/01/2005 and ToDate <=3D 01/01/2005 then I will =get
> only one record (ItemID=3D1). But ItemID =3D2 also lies in the same dater=ange,
> ie, daterange specified lies between 1900 and 9999. But the query will not
> give that result.
> Please let me know how to get this record also.
> Thanks in advance
I think you are storing dummy values in fromdate and todate column
when it is unknown (NULL)
If that is the case
(FromDate >=3D 01/01/2005 and ToDate < '01/01/2006' )
OR
FromDate >=3D 01/01/1900 and ToDate <=3D '01/01/9999' )
Note: < 01/01/2006 instead of <=3D 12/31/2005' to take care of time
portion
M A Srinivas

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 formatting

hi
below is my query AdditionalField3 is varchar datatype .and in this field my date is
storing like this 12/05/06..now i wanted to change format of this field AdditionalField3
like 2006-12-5

how to do this?
SELECT *
FROM Tbl_CMS_UploadDetails
WHERE AdditionalField3 = '2006-12-5'

You can use the following statement..

SELECT *
FROM Tbl_CMS_UploadDetails
WHERE Cast(AdditionalField3 as datetime) = Cast('2006-12-5' as datetime)

|||

i m getting below error

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

|||Ok.. What is your date format 12/05/06|||yes and that field is varchar|||

Ok..

If you use dd/mm/yy

SELECT *
FROM Tbl_CMS_UploadDetails
WHERE Convert(datetime, AdditionalField3, 103) = Cast('2006-12-5' as datetime)

if you use mm/dd/yy

SELECT *
FROM Tbl_CMS_UploadDetails
WHERE Convert(datetime, AdditionalField3, 101) = Cast('2006-12-5' as datetime)

|||

i tried both queries getting error

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

|||Is your AdditionalField3 has some invalid data?|||

yes this field also containing bank name ...we r using this field as common field so here for some cases we storing name and for other date thats why this field is varchar type ..for this purpose we r using format_id..

like this

for 83 we r storing date ..

SELECT *
FROM Tbl_CMS_UploadDetails
WHERE Convert(datetime, AdditionalField3, 101) = Cast('2006-12-5' as datetime) and format_id=83

|||

use the following query..

Code Snippet

--If you use dd/mm/yy

SET DATEFORMAT dmy

SELECT *

FROM Tbl_CMS_UploadDetails

WHERE Case When Isdate(AdditionalField3)=1 Then Convert(datetime, AdditionalField3, 103) Else NULL END = Cast('2006-12-5' as datetime)

--if you use mm/dd/yy

SET DATEFORMAT mdy

SELECT *

FROM Tbl_CMS_UploadDetails

WHERE Case When Isdate(AdditionalField3)=1 Then Convert(datetime, AdditionalField3, 101) Else NULL END = Cast('2006-12-5' as datetime)

|||

getting error

Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'Then'.

|||Ooops Fixed , try now|||

again error

Server: Msg 241, Level 16, State 1, Line 3
Syntax error converting datetime from character string.

|||thanx a lot....gotted now..

Date Format Question

Hi all - I have a question regarding formating getdate(). Below is my query.

SELECT

cast(datepart(yyyy, getdate()) as char(4))

+'-'+ cast(datepart(mm,getdate()) as char(2))

+'-'+ cast(datepart(dd, getdate()) as char(2))

Result

2007-5 -30

The result I would like is:

2007-05-30

Can anyone help me with this?

Thanks in Advance.

You could use:

Code Snippet

SELECT convert( varchar(10), getdate(), 120 )

-
2007-05-30

Refer to Books Online, Topic: 'Cast and Convert' for the usage of the 'style' codes (the 120 above).

Date format problem

Hello all,
This has been bugging me for most of the day. The sql statement below works just fine. It displays the date in the correct format. But, i want to include the time as well.

SELECT id, CONVERT(varchar(10), request_id) + Space(1) + CONVERT(varchar(20), date, 101) AS REQUEST, seen FROM Notes WHERE seen = '0'

Any ideas?
Thanks in advance.

Richard M.

SELECT id, CONVERT(varchar(10), request_id) + Space(1) + CONVERT(varchar(20), date, 101) + Space(1) + CONVERT(varchar(20), date, 108) AS REQUEST, seen FROM Notes WHERE seen = '0'

|||Thanks. I can't believe it was that simple.

Richard M.

Date Conversion

I need some help with Date Conversions. I am getting the following error
when I run the query below.
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
declare @.inputdate char(10)
declare @.tempdate datetime
declare @.validdate char(10)
SET @.inputdate = '15/05/2004'
SET @.tempdate = (SELECT convert (char(10), Date, 103) FROM [The Company -
Dev$Base Calendar Change]
WHERE Date = @.InputDate and Nonworking = '1')
if @.tempdate is not null
SET @.validdate = '0' else SET @.validdate = '1'
select @.validdateHi,
Infact you dont require a conversion in that place, because it seems the
Date field in the table is datetime datatype and @.tempdate variable also a
datetime datype.
In this case you dont require a convert.
Incase you need to convert please use 101 instead of 103.
Thanks
Hari
MCDBA
"Sarah" <skingswell@.donotreply.com> wrote in message
news:u6RnNUR$DHA.220@.TK2MSFTNGP09.phx.gbl...
> I need some help with Date Conversions. I am getting the following error
> when I run the query below.
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> declare @.inputdate char(10)
> declare @.tempdate datetime
> declare @.validdate char(10)
> SET @.inputdate = '15/05/2004'
> SET @.tempdate = (SELECT convert (char(10), Date, 103) FROM [The
Company -
> Dev$Base Calendar Change]
> WHERE Date = @.InputDate and Nonworking = '1')
> if @.tempdate is not null
> SET @.validdate = '0' else SET @.validdate = '1'
> select @.validdate
>|||The problem is how the
SET @.inputdate = '15/05/2004
is converted. This is based on the Lanaguage of the Login (unless overridden)
You can either override the date setting,
SET DATEFORMAT dm
Or use a neutral date formats (ISO and ISO8601
SET @.inputdate = '20040515' -- IS
SET @.inputdate = '2004-05-15T00:00.000' -- ISO8601
When using ISO8601, the 'T' must be there or else you will get very stange results
With a DMY connection, it reads it as YDM, which no one uses
With a MDY connection, it reads it as YMD, with is the ODBC standard|||The problem that I am having is the format of the date because if I actually
look at the Date values in the table with the enterprise manager they are in
the same format as the @.tempdate variable
25/05/2004
but the select query returns 2004-05-25 00:00:00. This is why I am
converting the select results. I still cannot get this working.
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OUg2CeR$DHA.1452@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Infact you dont require a conversion in that place, because it seems the
> Date field in the table is datetime datatype and @.tempdate variable also a
> datetime datype.
> In this case you dont require a convert.
> Incase you need to convert please use 101 instead of 103.
> Thanks
> Hari
> MCDBA
> "Sarah" <skingswell@.donotreply.com> wrote in message
> news:u6RnNUR$DHA.220@.TK2MSFTNGP09.phx.gbl...
> > I need some help with Date Conversions. I am getting the following
error
> > when I run the query below.
> >
> > The conversion of a char data type to a datetime data type resulted in
an
> > out-of-range datetime value.
> >
> > declare @.inputdate char(10)
> > declare @.tempdate datetime
> > declare @.validdate char(10)
> >
> > SET @.inputdate = '15/05/2004'
> >
> > SET @.tempdate = (SELECT convert (char(10), Date, 103) FROM [The
> Company -
> > Dev$Base Calendar Change]
> > WHERE Date = @.InputDate and Nonworking = '1')
> > if @.tempdate is not null
> > SET @.validdate = '0' else SET @.validdate = '1'
> > select @.validdate
> >
> >
>|||Excellent. Thanks very much. I used the SET DATEFORMAT dmy in the query and
it works perfectly.
"Al" <al_davie@.hotmail.com> wrote in message
news:E5B3103E-BDCC-4132-9006-7E59B6615214@.microsoft.com...
> The problem is how the
> SET @.inputdate = '15/05/2004'
> is converted. This is based on the Lanaguage of the Login (unless
overridden).
> You can either override the date setting,
> SET DATEFORMAT dmy
> Or use a neutral date formats (ISO and ISO8601)
> SET @.inputdate = '20040515' -- ISO
> SET @.inputdate = '2004-05-15T00:00.000' -- ISO8601
> When using ISO8601, the 'T' must be there or else you will get very stange
results.
> With a DMY connection, it reads it as YDM, which no one uses.
> With a MDY connection, it reads it as YMD, with is the ODBC standard.
>

Date Conversion

I need some help with Date Conversions. I am getting the following error
when I run the query below.
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
declare @.inputdate char(10)
declare @.tempdate datetime
declare @.validdate char(10)
SET @.inputdate = '15/05/2004'
SET @.tempdate = (SELECT convert (char(10), Date, 103) FROM [The Compan
y -
Dev$Base Calendar Change]
WHERE Date = @.InputDate and Nonworking = '1')
if @.tempdate is not null
SET @.validdate = '0' else SET @.validdate = '1'
select @.validdateHi,
Infact you dont require a conversion in that place, because it seems the
Date field in the table is datetime datatype and @.tempdate variable also a
datetime datype.
In this case you dont require a convert.
Incase you need to convert please use 101 instead of 103.
Thanks
Hari
MCDBA
"Sarah" <skingswell@.donotreply.com> wrote in message
news:u6RnNUR$DHA.220@.TK2MSFTNGP09.phx.gbl...
> I need some help with Date Conversions. I am getting the following error
> when I run the query below.
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> declare @.inputdate char(10)
> declare @.tempdate datetime
> declare @.validdate char(10)
> SET @.inputdate = '15/05/2004'
> SET @.tempdate = (SELECT convert (char(10), Date, 103) FROM [The
Company -
> Dev$Base Calendar Change]
> WHERE Date = @.InputDate and Nonworking = '1')
> if @.tempdate is not null
> SET @.validdate = '0' else SET @.validdate = '1'
> select @.validdate
>|||The problem is how the
SET @.inputdate = '15/05/2004'
is converted. This is based on the Lanaguage of the Login (unless overridden
).
You can either override the date setting,
SET DATEFORMAT dmy
Or use a neutral date formats (ISO and ISO8601)
SET @.inputdate = '20040515' -- ISO
SET @.inputdate = '2004-05-15T00:00.000' -- ISO8601
When using ISO8601, the 'T' must be there or else you will get very stange r
esults.
With a DMY connection, it reads it as YDM, which no one uses.
With a MDY connection, it reads it as YMD, with is the ODBC standard.|||The problem that I am having is the format of the date because if I actually
look at the Date values in the table with the enterprise manager they are in
the same format as the @.tempdate variable
25/05/2004
but the select query returns 2004-05-25 00:00:00. This is why I am
converting the select results. I still cannot get this working.
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OUg2CeR$DHA.1452@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Infact you dont require a conversion in that place, because it seems the
> Date field in the table is datetime datatype and @.tempdate variable also a
> datetime datype.
> In this case you dont require a convert.
> Incase you need to convert please use 101 instead of 103.
> Thanks
> Hari
> MCDBA
> "Sarah" <skingswell@.donotreply.com> wrote in message
> news:u6RnNUR$DHA.220@.TK2MSFTNGP09.phx.gbl...
error
an
> Company -
>|||Excellent. Thanks very much. I used the SET DATEFORMAT dmy in the query and
it works perfectly.
"Al" <al_davie@.hotmail.com> wrote in message
news:E5B3103E-BDCC-4132-9006-7E59B6615214@.microsoft.com...
> The problem is how the
> SET @.inputdate = '15/05/2004'
> is converted. This is based on the Lanaguage of the Login (unless
overridden).
> You can either override the date setting,
> SET DATEFORMAT dmy
> Or use a neutral date formats (ISO and ISO8601)
> SET @.inputdate = '20040515' -- ISO
> SET @.inputdate = '2004-05-15T00:00.000' -- ISO8601
> When using ISO8601, the 'T' must be there or else you will get very stange
results.
> With a DMY connection, it reads it as YDM, which no one uses.
> With a MDY connection, it reads it as YMD, with is the ODBC standard.
>

Date calculation...please help...

Hi,
I'm trying to find the difference between two dates (w/times) in terms of hours...can someone please modify the below pseudo-query so it does that, thanks.
select (date1 - date2)
from table1;Hi,

Already you have posted this query in the ORACLE Forum.|||u have to use it like this

select (date2-date1) * 24 * 60 from !!!! to get in minutes

because date2-date1 gives u a value in number of days..
multiply it by 24 gives you number of hours
multiply it by 24 * 60 gives you number of minutes
multiply it by 24 * 60 *60 gives you number of seconds

regards

Date calculation stored in table to be executed by SP

Hi
I've looked far and wide across a number of forums to find an answer to
the query below, but don't seem to have had any luck. If you know of
any existing posts already dealing with this issue, I'd be grateful for
any pointers.
I have a table STD_REPORTS_LIST in a SQL2k database. The table records
identify reports that a user of an ASP/SQL app can run; each record
points at a given SP which will generate a set of results to return to
the user.
Each record covers a given pre-defined time period: Today, This Week,
This Month, This Quarter, This Year YTD etc. To calculate the
appropriate start and end dates for the reports, two formulae are
stored against each record: the first formula determines the start date
of the report; the second formula determines the end date. The
formulae are stored in varchar fields.
Example data:
SRL_ID 1
SRL_TITLE This Week
SRL_SP_NAME stat_report_prod_views
SRL_START_DATE DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)
SRL_END_DATE DATEADD(wk, DATEDIFF(wk, 5,GETDATE()), 5)
So the example report above should run SP named stat_report_prod_views
supplying it with start and end dates for the current week.
When a report is selected by the user at the front-end (e.g. "this
week's user activity"):
1. the report ID is passed from ASP to a SP named run_stat_report.
2. the fields for the relevant record are pulled from STD_REPORTS_LIST.
3. the date formulae fields (SRL_START_DATE and SRL_END_DATE) are
SELECTed from the table,
4. and the dates generated by the formulae SHOULD be passed to the SP
in question (stat_report_prod_views, in the example above).
However, when I pull the fields into the SP using SELECT, the formula
itself comes into the SP as it's a varchar.. it doesn't get executed
... whereas if I run
SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6) then I get the
appropriate dates returned (e.g. 01/01/06 and 07/01/06) ... so the
question is: how can I return or calculate the date within the SP based
on the formula in question, ready to pass on to the secondary SP? i.e.
how can I execute the formula rather than just pulling it from the
table?
Please let me know if this is unclear or you require further details to
assist.
Best regards
OwenHi Owen
To use the formula you will need to use dynamic-sql, see
http://www.sommarskog.se/dynamic_sql.html for examples including how to use
sp_executesql to return a value.
John
<owain.williams@.medrus-consulting.co.uk> wrote in message
news:1136234972.052972.49150@.g44g2000cwa.googlegroups.com...
> Hi
> I've looked far and wide across a number of forums to find an answer to
> the query below, but don't seem to have had any luck. If you know of
> any existing posts already dealing with this issue, I'd be grateful for
> any pointers.
> I have a table STD_REPORTS_LIST in a SQL2k database. The table records
> identify reports that a user of an ASP/SQL app can run; each record
> points at a given SP which will generate a set of results to return to
> the user.
> Each record covers a given pre-defined time period: Today, This Week,
> This Month, This Quarter, This Year YTD etc. To calculate the
> appropriate start and end dates for the reports, two formulae are
> stored against each record: the first formula determines the start date
> of the report; the second formula determines the end date. The
> formulae are stored in varchar fields.
> Example data:
> SRL_ID 1
> SRL_TITLE This Week
> SRL_SP_NAME stat_report_prod_views
> SRL_START_DATE DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)
> SRL_END_DATE DATEADD(wk, DATEDIFF(wk, 5,GETDATE()), 5)
> So the example report above should run SP named stat_report_prod_views
> supplying it with start and end dates for the current week.
> When a report is selected by the user at the front-end (e.g. "this
> week's user activity"):
> 1. the report ID is passed from ASP to a SP named run_stat_report.
> 2. the fields for the relevant record are pulled from STD_REPORTS_LIST.
> 3. the date formulae fields (SRL_START_DATE and SRL_END_DATE) are
> SELECTed from the table,
> 4. and the dates generated by the formulae SHOULD be passed to the SP
> in question (stat_report_prod_views, in the example above).
> However, when I pull the fields into the SP using SELECT, the formula
> itself comes into the SP as it's a varchar.. it doesn't get executed
> ... whereas if I run
> SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6) then I get the
> appropriate dates returned (e.g. 01/01/06 and 07/01/06) ... so the
> question is: how can I return or calculate the date within the SP based
> on the formula in question, ready to pass on to the secondary SP? i.e.
> how can I execute the formula rather than just pulling it from the
> table?
> Please let me know if this is unclear or you require further details to
> assist.
>
> Best regards
> Owen
>|||Hi John,
Thanks for the feedback... I'd heard once or twice of Dynamic SQL but
didn't know much about it... looks like this is going to be very useful
!!
I'll update the post if I need any further info but many thanks in
advance.
Kind regards

Date calculation stored in table to be executed by SP

Hi
I've looked far and wide across a number of forums to find an answer to
the query below, but don't seem to have had any luck. If you know of
any existing posts already dealing with this issue, I'd be grateful for
any pointers.
I have a table STD_REPORTS_LIST in a SQL2k database. The table records
identify reports that a user of an ASP/SQL app can run; each record
points at a given SP which will generate a set of results to return to
the user.
Each record covers a given pre-defined time period: Today, This Week,
This Month, This Quarter, This Year YTD etc. To calculate the
appropriate start and end dates for the reports, two formulae are
stored against each record: the first formula determines the start date
of the report; the second formula determines the end date. The
formulae are stored in varchar fields.
Example data:
SRL_ID 1
SRL_TITLE This Week
SRL_SP_NAME stat_report_prod_views
SRL_START_DATE DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)
SRL_END_DATE DATEADD(wk, DATEDIFF(wk, 5,GETDATE()), 5)
So the example report above should run SP named stat_report_prod_views
supplying it with start and end dates for the current week.
When a report is selected by the user at the front-end (e.g. "this
week's user activity"):
1. the report ID is passed from ASP to a SP named run_stat_report.
2. the fields for the relevant record are pulled from STD_REPORTS_LIST.
3. the date formulae fields (SRL_START_DATE and SRL_END_DATE) are
SELECTed from the table,
4. and the dates generated by the formulae SHOULD be passed to the SP
in question (stat_report_prod_views, in the example above).
However, when I pull the fields into the SP using SELECT, the formula
itself comes into the SP as it's a varchar.. it doesn't get executed
... whereas if I run
SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6) then I get the
appropriate dates returned (e.g. 01/01/06 and 07/01/06) ... so the
question is: how can I return or calculate the date within the SP based
on the formula in question, ready to pass on to the secondary SP? i.e.
how can I execute the formula rather than just pulling it from the
table?
Please let me know if this is unclear or you require further details to
assist.
Best regards
OwenHi Owen
To use the formula you will need to use dynamic-sql, see
http://www.sommarskog.se/dynamic_sql.html for examples including how to use
sp_executesql to return a value.
John
<owain.williams@.medrus-consulting.co.uk> wrote in message
news:1136234972.052972.49150@.g44g2000cwa.googlegroups.com...
> Hi
> I've looked far and wide across a number of forums to find an answer to
> the query below, but don't seem to have had any luck. If you know of
> any existing posts already dealing with this issue, I'd be grateful for
> any pointers.
> I have a table STD_REPORTS_LIST in a SQL2k database. The table records
> identify reports that a user of an ASP/SQL app can run; each record
> points at a given SP which will generate a set of results to return to
> the user.
> Each record covers a given pre-defined time period: Today, This Week,
> This Month, This Quarter, This Year YTD etc. To calculate the
> appropriate start and end dates for the reports, two formulae are
> stored against each record: the first formula determines the start date
> of the report; the second formula determines the end date. The
> formulae are stored in varchar fields.
> Example data:
> SRL_ID 1
> SRL_TITLE This Week
> SRL_SP_NAME stat_report_prod_views
> SRL_START_DATE DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)
> SRL_END_DATE DATEADD(wk, DATEDIFF(wk, 5,GETDATE()), 5)
> So the example report above should run SP named stat_report_prod_views
> supplying it with start and end dates for the current week.
> When a report is selected by the user at the front-end (e.g. "this
> week's user activity"):
> 1. the report ID is passed from ASP to a SP named run_stat_report.
> 2. the fields for the relevant record are pulled from STD_REPORTS_LIST.
> 3. the date formulae fields (SRL_START_DATE and SRL_END_DATE) are
> SELECTed from the table,
> 4. and the dates generated by the formulae SHOULD be passed to the SP
> in question (stat_report_prod_views, in the example above).
> However, when I pull the fields into the SP using SELECT, the formula
> itself comes into the SP as it's a varchar.. it doesn't get executed
> ... whereas if I run
> SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6) then I get the
> appropriate dates returned (e.g. 01/01/06 and 07/01/06) ... so the
> question is: how can I return or calculate the date within the SP based
> on the formula in question, ready to pass on to the secondary SP? i.e.
> how can I execute the formula rather than just pulling it from the
> table?
> Please let me know if this is unclear or you require further details to
> assist.
>
> Best regards
> Owen
>|||Hi John,
Thanks for the feedback... I'd heard once or twice of Dynamic SQL but
didn't know much about it... looks like this is going to be very useful
!!
I'll update the post if I need any further info but many thanks in
advance.
Kind regards

Date calculation stored in table to be executed by SP

Hi
I've looked far and wide across a number of forums to find an answer to
the query below, but don't seem to have had any luck. If you know of
any existing posts already dealing with this issue, I'd be grateful for
any pointers.
I have a table STD_REPORTS_LIST in a SQL2k database. The table records
identify reports that a user of an ASP/SQL app can run; each record
points at a given SP which will generate a set of results to return to
the user.
Each record covers a given pre-defined time period: Today, This Week,
This Month, This Quarter, This Year YTD etc. To calculate the
appropriate start and end dates for the reports, two formulae are
stored against each record: the first formula determines the start date
of the report; the second formula determines the end date. The
formulae are stored in varchar fields.
Example data:
SRL_ID 1
SRL_TITLE This Week
SRL_SP_NAME stat_report_prod_views
SRL_START_DATE DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)
SRL_END_DATE DATEADD(wk, DATEDIFF(wk, 5,GETDATE()), 5)
So the example report above should run SP named stat_report_prod_views
supplying it with start and end dates for the current week.
When a report is selected by the user at the front-end (e.g. "this
week's user activity"):
1. the report ID is passed from ASP to a SP named run_stat_report.
2. the fields for the relevant record are pulled from STD_REPORTS_LIST.
3. the date formulae fields (SRL_START_DATE and SRL_END_DATE) are
SELECTed from the table,
4. and the dates generated by the formulae SHOULD be passed to the SP
in question (stat_report_prod_views, in the example above).
However, when I pull the fields into the SP using SELECT, the formula
itself comes into the SP as it's a varchar.. it doesn't get executed
... whereas if I run
SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6) then I get the
appropriate dates returned (e.g. 01/01/06 and 07/01/06) ... so the
question is: how can I return or calculate the date within the SP based
on the formula in question, ready to pass on to the secondary SP? i.e.
how can I execute the formula rather than just pulling it from the
table?
Please let me know if this is unclear or you require further details to
assist.
Best regards
Owen
Hi Owen
To use the formula you will need to use dynamic-sql, see
http://www.sommarskog.se/dynamic_sql.html for examples including how to use
sp_executesql to return a value.
John
<owain.williams@.medrus-consulting.co.uk> wrote in message
news:1136234972.052972.49150@.g44g2000cwa.googlegro ups.com...
> Hi
> I've looked far and wide across a number of forums to find an answer to
> the query below, but don't seem to have had any luck. If you know of
> any existing posts already dealing with this issue, I'd be grateful for
> any pointers.
> I have a table STD_REPORTS_LIST in a SQL2k database. The table records
> identify reports that a user of an ASP/SQL app can run; each record
> points at a given SP which will generate a set of results to return to
> the user.
> Each record covers a given pre-defined time period: Today, This Week,
> This Month, This Quarter, This Year YTD etc. To calculate the
> appropriate start and end dates for the reports, two formulae are
> stored against each record: the first formula determines the start date
> of the report; the second formula determines the end date. The
> formulae are stored in varchar fields.
> Example data:
> SRL_ID 1
> SRL_TITLE This Week
> SRL_SP_NAME stat_report_prod_views
> SRL_START_DATE DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)
> SRL_END_DATE DATEADD(wk, DATEDIFF(wk, 5,GETDATE()), 5)
> So the example report above should run SP named stat_report_prod_views
> supplying it with start and end dates for the current week.
> When a report is selected by the user at the front-end (e.g. "this
> week's user activity"):
> 1. the report ID is passed from ASP to a SP named run_stat_report.
> 2. the fields for the relevant record are pulled from STD_REPORTS_LIST.
> 3. the date formulae fields (SRL_START_DATE and SRL_END_DATE) are
> SELECTed from the table,
> 4. and the dates generated by the formulae SHOULD be passed to the SP
> in question (stat_report_prod_views, in the example above).
> However, when I pull the fields into the SP using SELECT, the formula
> itself comes into the SP as it's a varchar.. it doesn't get executed
> ... whereas if I run
> SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6) then I get the
> appropriate dates returned (e.g. 01/01/06 and 07/01/06) ... so the
> question is: how can I return or calculate the date within the SP based
> on the formula in question, ready to pass on to the secondary SP? i.e.
> how can I execute the formula rather than just pulling it from the
> table?
> Please let me know if this is unclear or you require further details to
> assist.
>
> Best regards
> Owen
>
|||Hi John,
Thanks for the feedback... I'd heard once or twice of Dynamic SQL but
didn't know much about it... looks like this is going to be very useful
!!
I'll update the post if I need any further info but many thanks in
advance.
Kind regards

Date and time stamp

Hi im having a few problems saving a file with the data and time i am
using the below t-sql but can only get the date and not the time.
CONVERT(NVARCHAR(16),GETDATE(),112)
Any guidance would very much be appreciated.
thanks in advanceWhat is the exact format you need it in? 112 converts to a CHAR(8)
basically, YYYYMMDD.
For some other possibilities, see http://www.aspfaq.com/2464
"blueboy" <matt_meech@.hotmail.com> wrote in message
news:1140636158.729737.51920@.f14g2000cwb.googlegroups.com...
> Hi im having a few problems saving a file with the data and time i am
> using the below t-sql but can only get the date and not the time.
> CONVERT(NVARCHAR(16),GETDATE(),112)
> Any guidance would very much be appreciated.
> thanks in advance
>|||convert(varchar(50),getdate(),9)
If you look towards the bottom of my blog, I have compiled a list of
diffrent datetime styles.
HTH
MJKulangara
http://sqladventures.blogspot.com|||There are many and you were on the right track for one way.. :)
CONVERT(NVARCHAR(16),GETDATE(),112) +
REPLACE(CONVERT(NVARCHAR(12),GETDATE(),1
14),':','')
Good luck|||hehe you rated your own post :P|||Unintentional...apparently just clicking on it by accident does it.
Don't touch my hubris :-)|||The format i posted outputs
20060222150720407
year
2006
month
02
date
22
hour
15
minute
07
second
20
millisecond
407
then you can append .txt to the end of that|||MJKulangara wrote:
> Unintentional...apparently just clicking on it by accident does it.
> Don't touch my hubris :-)
Gmail is so <3 gmail|||Jebuskrust,
Many thanks that has gave me the above format but idealy i would like
just 200602221507
year
2006
month
02
date
22
hour
15
minute
07
Can this be done'|||yes change the nvarchar(12) to a nvarchar(8) to show instead of 12
chars to show the first 8 instead. (cheers!)