getdate

Hi
I have to create a series of reports that look back over certain days ie.
day before
current w
current month, quarter, year
The reports will be scheduled to be run between the hours of 10pm to 6am
when the business is shut.
I am using getdate - days to get my time span an example is
WHERE (dbo.vw_MIS_AppWritten.Date_App_Written BETWEEN CONVERT(datetime,
CONVERT(varchar(11), GETDATE() - 1, 102), 102) AND CONVERT(datetime,
CONVERT(varchar(11), GETDATE() + 0, 102), 102))
this would get me my range for yesterdays results
However having run a query with the above where clause it pulled some out
for today, which make smy results wrong because it has used some of today, i
am assuming it used 2006-01-05 16:30:32 as the getdate and took off 24 hours
giving me 2006-01-04 16:30:32 , this may not be a problem if the reports are
scheduled to run out of hours but is there a way of using the date and
adding my own time constraint in eg 00:00:01 and 23:59:59 this would make
sure my reports were accurate, plus it would be nice to know
hope i made sense
regardsHave a look at the DATEDIFF function is SQL Books
HTH. Ryan
"Steven Scaife" <sp@.nospam.com> wrote in message
news:ey0ywdhEGHA.140@.TK2MSFTNGP12.phx.gbl...
> Hi
> I have to create a series of reports that look back over certain days ie.
> day before
> current w
> current month, quarter, year
> The reports will be scheduled to be run between the hours of 10pm to 6am
> when the business is shut.
> I am using getdate - days to get my time span an example is
> WHERE (dbo.vw_MIS_AppWritten.Date_App_Written BETWEEN
> CONVERT(datetime, CONVERT(varchar(11), GETDATE() - 1, 102), 102) AND
> CONVERT(datetime,
> CONVERT(varchar(11), GETDATE() + 0, 102), 102))
> this would get me my range for yesterdays results
> However having run a query with the above where clause it pulled some out
> for today, which make smy results wrong because it has used some of today,
> i am assuming it used 2006-01-05 16:30:32 as the getdate and took off 24
> hours giving me 2006-01-04 16:30:32 , this may not be a problem if the
> reports are scheduled to run out of hours but is there a way of using the
> date and adding my own time constraint in eg 00:00:01 and 23:59:59 this
> would make sure my reports were accurate, plus it would be nice to know
> hope i made sense
> regards
>|||Hope that following syntax can help you. This will give yout todays date at
10:00 PM.
select convert(datetime,convert(char(8),getdate
(),112) + ' 22:00:00',120)
"Steven Scaife" wrote:

> Hi
> I have to create a series of reports that look back over certain days ie.
> day before
> current w
> current month, quarter, year
> The reports will be scheduled to be run between the hours of 10pm to 6am
> when the business is shut.
> I am using getdate - days to get my time span an example is
> WHERE (dbo.vw_MIS_AppWritten.Date_App_Written BETWEEN CONVERT(datetime
,
> CONVERT(varchar(11), GETDATE() - 1, 102), 102) AND CONVERT(datetime,
> CONVERT(varchar(11), GETDATE() + 0, 102), 102))
> this would get me my range for yesterdays results
> However having run a query with the above where clause it pulled some out
> for today, which make smy results wrong because it has used some of today,
i
> am assuming it used 2006-01-05 16:30:32 as the getdate and took off 24 hou
rs
> giving me 2006-01-04 16:30:32 , this may not be a problem if the reports a
re
> scheduled to run out of hours but is there a way of using the date and
> adding my own time constraint in eg 00:00:01 and 23:59:59 this would make
> sure my reports were accurate, plus it would be nice to know
> hope i made sense
> regards
>
>|||For yesterday's results, try
WHERE DATEDIFF(dd, dbo.vw_MIS_AppWritten.Date_App_Written, GETDATE()) = 1
For last w's results
WHERE DATEDIFF(wk, dbo.vw_MIS_AppWritten.Date_App_Written, GETDATE()) = 1
For last month's results
WHERE DATEDIFF(mm, dbo.vw_MIS_AppWritten.Date_App_Written, GETDATE()) = 1
"Steven Scaife" wrote:

> Hi
> I have to create a series of reports that look back over certain days ie.
> day before
> current w
> current month, quarter, year
> The reports will be scheduled to be run between the hours of 10pm to 6am
> when the business is shut.
> I am using getdate - days to get my time span an example is
> WHERE (dbo.vw_MIS_AppWritten.Date_App_Written BETWEEN CONVERT(datetime
,
> CONVERT(varchar(11), GETDATE() - 1, 102), 102) AND CONVERT(datetime,
> CONVERT(varchar(11), GETDATE() + 0, 102), 102))
> this would get me my range for yesterdays results
> However having run a query with the above where clause it pulled some out
> for today, which make smy results wrong because it has used some of today,
i
> am assuming it used 2006-01-05 16:30:32 as the getdate and took off 24 hou
rs
> giving me 2006-01-04 16:30:32 , this may not be a problem if the reports a
re
> scheduled to run out of hours but is there a way of using the date and
> adding my own time constraint in eg 00:00:01 and 23:59:59 this would make
> sure my reports were accurate, plus it would be nice to know
> hope i made sense
> regards
>
>|||Also you can use a calendar table|||There are several ways to remove the timestamp from getdate() and retaining
it as a datetime that I know of.
Here is what I typically do
1) SELECT CAST(CONVERT(VARCHAR(10), getdate(), 102) as DATETIME)
I would avoid the "adding your own time" and just use comparisons against
dates without times (ie. Midnight of that day).
For Instance, if i wanted all rows where dateFromTable is Today the WHERE
clause would be:
WHERE dateFromTable BETWEEN CAST(CONVERT(VARCHAR(10), getdate(), 102) as
DATETIME)
AND CAST(CONVERT(VARCHAR(10), dateadd(dd, 1, getdate()), 102) as DATETIME)
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Steven Scaife" wrote:

> Hi
> I have to create a series of reports that look back over certain days ie.
> day before
> current w
> current month, quarter, year
> The reports will be scheduled to be run between the hours of 10pm to 6am
> when the business is shut.
> I am using getdate - days to get my time span an example is
> WHERE (dbo.vw_MIS_AppWritten.Date_App_Written BETWEEN CONVERT(datetime
,
> CONVERT(varchar(11), GETDATE() - 1, 102), 102) AND CONVERT(datetime,
> CONVERT(varchar(11), GETDATE() + 0, 102), 102))
> this would get me my range for yesterdays results
> However having run a query with the above where clause it pulled some out
> for today, which make smy results wrong because it has used some of today,
i
> am assuming it used 2006-01-05 16:30:32 as the getdate and took off 24 hou
rs
> giving me 2006-01-04 16:30:32 , this may not be a problem if the reports a
re
> scheduled to run out of hours but is there a way of using the date and
> adding my own time constraint in eg 00:00:01 and 23:59:59 this would make
> sure my reports were accurate, plus it would be nice to know
> hope i made sense
> regards
>
>|||This works well too
SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
Returns today's date at 00:00:00.000
"Ryan Powers" wrote:
> There are several ways to remove the timestamp from getdate() and retainin
g
> it as a datetime that I know of.
> Here is what I typically do
> 1) SELECT CAST(CONVERT(VARCHAR(10), getdate(), 102) as DATETIME)
> I would avoid the "adding your own time" and just use comparisons against
> dates without times (ie. Midnight of that day).
> For Instance, if i wanted all rows where dateFromTable is Today the WHERE
> clause would be:
> WHERE dateFromTable BETWEEN CAST(CONVERT(VARCHAR(10), getdate(), 102) as
> DATETIME)
> AND CAST(CONVERT(VARCHAR(10), dateadd(dd, 1, getdate()), 102) as DATETIME)
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "Steven Scaife" wrote:
>|||Nice. Thanks Mark.
That is a little cleaner that what I proposed. Good to know.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark Williams" wrote:
> This works well too
> SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
> Returns today's date at 00:00:00.000
> --
> "Ryan Powers" wrote:
>|||thank you it is much appreciated
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:0DBCD236-D731-4201-B577-558AA3708FE3@.microsoft.com...
> This works well too
> SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
> Returns today's date at 00:00:00.000
> --
> "Ryan Powers" wrote:
>

GetChanges Update problem

I have the below C# routine which is working but now I need to write its newly randomized L_Rank values back up to the Sql Server. How can i do that in Button1_Click? DataTable 'dt' contains only two fields - L_ID and L_Rank. I have a stored procedure but I do not know how to call it in Button1_Click and pass the parameters it is looking for.

SP:
PROCEDURE RandomizeLinks
@.L_ID int,
@.L_Rank int
AS
UPDATE tblLinkInfo_OLD2 SET L_Rank = @.L_Rank
WHERE (L_ID = @.L_ID)

protected void Button1_Click(object sender, EventArgs e)
{
GetRandLinks();
DataTable dt = GetRandLinks();
int RowIncrement;
RowIncrement = 0;
System.Random myRandom = new System.Random();
foreach (DataRow row in dt.Rows)
{
int LinkRank = myRandom.Next(25, 250);
row["L_Rank"] = LinkRank;
RowIncrement++;
}

Is what I want to do possible - it seems easy but nothing I do works.

|||

Am I asking the question wrong - Can you use a DataTable to update a SQL Table and can it be done in a batch UPDATE as opposed to incrementing through every row?

|||

anyone?

Get XML from SQL Server 2000

I have a stored procedure, that returns a customer record from the customers table in the northwind database.
how can i return back an xml string of the row?
I mean, when the aspx page calls that procudure, I want to have somehting like:
<customers>
<customer>
<customerid>xxx</customerid>
<companyname>rrr</companyname>
</customer>
</customers>
can a stored procedure return such a string in xml form?
thanks alot

Read up on the FOR XML clause. Here's an article:http://www.sqljunkies.com/Article/296D1B56-8BDD-4236-808F-E62CC1908C4E.scuk and there's quite a bit of good info in BOL.

get todays date and a certain time

Hi Everyone,

I am trying to write something to give me back all the data for a
sertain time range for today.
So for example: I need to get all records where change_date is <= today
2pm and today at 8pm.
I know i can get just the date for today by using
CONVERT(CHAR(10),getdate(),102) but can i add a time range to that?

Thanks in advance,
AnnaYou can use DATEADD, for example:

SELECT DATEADD(hour,14,CONVERT(CHAR(10),getdate(),102))

Razvan

AKorsakova@.gmail.com wrote:

Quote:

Originally Posted by

Hi Everyone,
>
I am trying to write something to give me back all the data for a
sertain time range for today.
So for example: I need to get all records where change_date is <= today
2pm and today at 8pm.
I know i can get just the date for today by using
CONVERT(CHAR(10),getdate(),102) but can i add a time range to that?
>
Thanks in advance,
Anna

|||On 27 Sep 2006 11:36:59 -0700, AKorsakova@.gmail.com wrote:

Quote:

Originally Posted by

>Hi Everyone,
>
>I am trying to write something to give me back all the data for a
>sertain time range for today.
>So for example: I need to get all records where change_date is <= today
>2pm and today at 8pm.
>I know i can get just the date for today by using
>CONVERT(CHAR(10),getdate(),102) but can i add a time range to that?

Hi Anna,

Use either

CONVERT(datetime, CONVERT(CHAR(10), getdate(), 126) + 'T14:00:00')

or

DATEADD(day, DATEDIFF(day, 0, getdate()), '14:00:00')

to get current date with a time of 2PM.

--
Hugo Kornelis, SQL Server MVP

get the row counts for each day going back to 6 months (was "query help")

I have a proc to get the rowcounts for the given date range.
I have to get the row counts for each day going back to 6 months on the table.

With this proc i can get one day's row couts.. i need to loop through for all dates.

Please can someone get me the code for this.

create proc p_rowcounts

@.Date1 datetime,
@.Date2 datetime

SELECT
count (*) as 'Number of Rows', @.Date1 as Date
FROM
Table1 (nolock)
WHERE ModifyTime >= @.Date1 and ModifyTime < @.Date2

thanks for the help.I'd do it as:CREATE PROC p_rowcounts
@.Date1 datetime = NULL
, @.Date2 datetime = NULL
AS

IF @.Date1 IS NULL SET @.Date1 = GetDate()
IF @.Date2 IS NULL SET @.Date2 = DateAdd(month, -6, Convert(CHAR(10), @.Date1, 121))

SELECT
Count (*) AS 'Number of Rows'
, Convert(DATETIME, Convert(CHAR(10), ModifyTime, 121)) AS Date
FROM Table1 (nolock)
WHERE ModifyTime BETWEEN @.Date2 AND @.Date1
GROUP BY Convert(CHAR(10), ModifyTime, 121)

RETURN-PatP|||pat, i think sskris wants one count per date in the range|||That query ought to give one count per day in the range. I think you're hinting that you'd like to see rows with zeros for a count for days with no data, which I see as wasteful and poor practice.

If you have code that relies on zeros, you can certainly go to added trouble to make the zeros appear, but in my mind you'd be much better off to fix the code instead of writing SQL to cater to the problems in it.

-PatP

Get return value of stored procedure in Query Analyzer

Hi. We've got a stored procedure on SQL Server 2000 with a Return statement,
and sending back a number. Is there a way to view the Return value when
executing this procedure in Query Analyzer? Right now, it's just displaying
how many rows were affected. Thanks.What about a PRINT statement prior to your Return statement?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"dw" <cougarmana_NOSPAM_@.uncw.edu> wrote in message
news:u3NDF0dSGHA.792@.TK2MSFTNGP10.phx.gbl...
> Hi. We've got a stored procedure on SQL Server 2000 with a Return
statement,
> and sending back a number. Is there a way to view the Return value when
> executing this procedure in Query Analyzer? Right now, it's just
displaying
> how many rows were affected. Thanks.
>|||example
create proc prTestReturnValue
as
select getdate()
return 5
GO
declare @.i int
exec @.i =prTestReturnValue
select @.i
http://sqlservercode.blogspot.com/|||dw,
declare @.rv int
exec @.rv = dbo.p1 ...
select @.rv
go
See "execute" command/statement in BOL.
AMB
"dw" wrote:

> Hi. We've got a stored procedure on SQL Server 2000 with a Return statemen
t,
> and sending back a number. Is there a way to view the Return value when
> executing this procedure in Query Analyzer? Right now, it's just displayin
g
> how many rows were affected. Thanks.
>
>|||Thank you all for the answers. That's what I needed and it worked
beautifully :)
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:AF2BF839-61D5-48FE-9845-11532AFB82CF@.microsoft.com...
> dw,
> declare @.rv int
> exec @.rv = dbo.p1 ...
> select @.rv
> go
> See "execute" command/statement in BOL.
>
> AMB
> "dw" wrote:
>

Get report design from Report Manager

If you cannot find the current Visual Studio sln project file, can you obtain
the report design from the Report Manger to plug back into the designer to
ensure that you start from the most current report verision held on the
server?
Thanks
IanYes. Using Report Manager, select the report you want and from the
Properties tab click on the Edit link to download the rdl file. FYI -
there is a nice and free utility called Reporting Services Scripter by
Jasper Smith that can grab multiple files and download ot transfer them
to another RS - http://www.sqldbatips.com/showarticle.asp?ID=62
Matt A|||Thanks Matt I'd completely forgotten this Edit link facility
"reportdude" wrote:
> Yes. Using Report Manager, select the report you want and from the
> Properties tab click on the Edit link to download the rdl file. FYI -
> there is a nice and free utility called Reporting Services Scripter by
> Jasper Smith that can grab multiple files and download ot transfer them
> to another RS - http://www.sqldbatips.com/showarticle.asp?ID=62
> Matt A
>

get recordcount from Oracle and update sql server table

I posted this question a little while ago but was not able to implement it. Now I am back to the same issue. Basically I want to get a recordcount from a table in Oracle and update an existing record in sql server with the value.

I am trying to accomplish this using a Execute SQL Task. In this task I am pointing to a Oracle DB that I am able to query from SSIS so connectivity is not an issue.

I have defined a variable EmpRC of type int32.

I have a following the the SQL Task:

query: select count(*) from emp;

result set=single row.

and on result set tab ResultName =0 and variable name is same defined above : User::EmpRC

I get an error when I run this:

[Execute SQL Task] Error: An error occurred while assigning a value to variable "EmpCompRC": "Unsupported data type on result set binding 0.".

I have tried using different data types for EmpRC but having no luck. any ideas?

Not sure if this is a typo, but you keep refering to variable EmpRC, yet the error from ssis refers to a variable EmpCompRC.

Also, why don't you try using that sql statement in a data flow task to see what data type is assigned to it.

|||

Anthony Martin wrote:

Not sure if this is a typo, but you keep refering to variable EmpRC, yet the error from ssis refers to a variable EmpCompRC.

Also, why don't you try using that sql statement in a data flow task to see what data type is assigned to it.

Oracle doesn't have "integer" data types, that's why it doesn't work. Oracle only has NUMERIC data types, and when used without a precision, it is to be considered an "integer." It's a pain in the a$$ and I don't think Microsoft has any intentions of fixing the problem:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=264932

[Microsoft follow-up]|||

so is there any workaround? I am able to see the link but when click on view workaround it takes me to my msdn profile.

|||

Shahab03 wrote:

so is there any workaround? I am able to see the link but when click on view workaround it takes me to my msdn profile.

This is the referenced link in the workaround section of that Connect article:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PageIndex=2&SiteID=1&PostID=107027

I don't think it will help in the end though.
|||

that is a disappointment.

|||

well I figured out how to accomplish this. So hopefully everyone else wont have to bang their head on the 'wall of data types'.

1. well basically you will create a variable in SSIS of data type String.

2. you will have to convert the returning data type of select count(*) to a character in oracle. e.g.

select to_char(count(*)) from emp

3. create another SQL Task after the SQL Task for "count(*)". and in the parameter tab set the datatype to varchar with variable same as ResultSetName from previous SQL Task.

hope this is clear enough.

get recordcount from Oracle and update sql server table

I posted this question a little while ago but was not able to implement it. Now I am back to the same issue. Basically I want to get a recordcount from a table in Oracle and update an existing record in sql server with the value.

I am trying to accomplish this using a Execute SQL Task. In this task I am pointing to a Oracle DB that I am able to query from SSIS so connectivity is not an issue.

I have defined a variable EmpRC of type int32.

I have a following the the SQL Task:

query: select count(*) from emp;

result set=single row.

and on result set tab ResultName =0 and variable name is same defined above : User::EmpRC

I get an error when I run this:

[Execute SQL Task] Error: An error occurred while assigning a value to variable "EmpCompRC": "Unsupported data type on result set binding 0.".

I have tried using different data types for EmpRC but having no luck. any ideas?

Not sure if this is a typo, but you keep refering to variable EmpRC, yet the error from ssis refers to a variable EmpCompRC.

Also, why don't you try using that sql statement in a data flow task to see what data type is assigned to it.

|||

Anthony Martin wrote:

Not sure if this is a typo, but you keep refering to variable EmpRC, yet the error from ssis refers to a variable EmpCompRC.

Also, why don't you try using that sql statement in a data flow task to see what data type is assigned to it.

Oracle doesn't have "integer" data types, that's why it doesn't work. Oracle only has NUMERIC data types, and when used without a precision, it is to be considered an "integer." It's a pain in the a$$ and I don't think Microsoft has any intentions of fixing the problem:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=264932

[Microsoft follow-up]|||

so is there any workaround? I am able to see the link but when click on view workaround it takes me to my msdn profile.

|||

Shahab03 wrote:

so is there any workaround? I am able to see the link but when click on view workaround it takes me to my msdn profile.

This is the referenced link in the workaround section of that Connect article:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PageIndex=2&SiteID=1&PostID=107027

I don't think it will help in the end though.
|||

that is a disappointment.

|||

well I figured out how to accomplish this. So hopefully everyone else wont have to bang their head on the 'wall of data types'.

1. well basically you will create a variable in SSIS of data type String.

2. you will have to convert the returning data type of select count(*) to a character in oracle. e.g.

select to_char(count(*)) from emp

3. create another SQL Task after the SQL Task for "count(*)". and in the parameter tab set the datatype to varchar with variable same as ResultSetName from previous SQL Task.

hope this is clear enough.

Get my own Store procedure from Hosted SQL server.

I have design my web and host it at ISP SQL server, unfortunately I have lost my Store procedure scripts and want to get it back from that ISp server.

1. Is there anyway to extract the stored procedure without using SQL DTS [ I have try it but only can backup my tables content/data only]

2. I can change my asp scripts direct connect to my ISP SQL server without doubt just like what I have hosting in their's.[I know my own admin password,but not the procedure wrote.]

3. ASK? Which asp/VBScript command can extract from their server without using DTS?

Thanks in advanced.Originally posted by edmun3
RE: I have design my web and host it at ISP SQL server, unfortunately I have lost my Store procedure scripts and want to get it back from that ISp server.
1. Is there anyway to extract the stored procedure without using SQL DTS [ I have try it but only can backup my tables content/data only]
2. I can change my asp scripts direct connect to my ISP SQL server without doubt just like what I have hosting in their's.[I know my own admin password,but not the procedure wrote.]
3. ASK? Which asp/VBScript command can extract from their server without using DTS? Thanks in advanced.

Q1 [Is there any way to extract a stored procedure without using SQL DTS [ I have try it but only can backup my tables content/data only]]?
A1 Yes. since you can backup and access your tables, access YourDB..Syscomments. I beleive the syscomments column in which you will find your stored procedure tsql is named text.