|
Hi I need to write a function in SQL that returns the date of the previous Monday, for example:
if today is Tuesday thru Sunday then get previous Monday
else if today is Monday get todays date.
Any help would be appreciated.
Thanks.
|
|
|
|
|
I actually got this to work so I thot I'd share...
ALTER FUNCTION [dbo].[fn_GetMonday]
( @pInputDate DATETIME )
RETURNS DATETIME
BEGIN
DECLARE @Result DATETIME
SET @pInputDate = CONVERT(VARCHAR(10), @pInputDate, 111)
IF DATENAME(dw, @pInputDate) = 'Sunday'
BEGIN
SET @Result = DATEADD(DD, -6, @pInputDate)
END
ELSE
SET @Result = DATEADD(DD, -DATEPART(DW, @pInputDate) + 2, @pInputDate)
RETURN @Result
END
|
|
|
|
|
I am no expert and there may be a better way of doing it, but have a look at DATEPART and DATEADD functions along with a WHILE loop.
DECLARE @MyDate DATETIME<br />
SET @MyDate = GETDATE()<br />
WHILE (DATEPART(dw, @MyDate)) <> 'Monday'<br />
BEGIN<br />
SET @MyDate = DATEADD(day, -1, @MyDate)<br />
END
MyDate should now contain the date of the previous monday or today's date if today is a Monday.
Steve Jowett
-------------------------
It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)
|
|
|
|
|
I have an application in which I fill a listbox with values from a dataset. But the listbox is filled with 'system.data.datarowview' here the values are displayed correctly.But when the page is load for the 1st time tha list box showing 'system.data.datarowview'after click on dat dat coming..plz help me
i ve writen the code like
con.Open();
SqlDataAdapter adp= new SqlDataAdapter(lstrsql,con);
DataSet objds=new DataSet();
adp.Fill(objds);
lst_emp.DataTextField=objds.Tables[0].Columns["txtval"].ToString();
lst_emp.DataValueField=objds.Tables[0].Columns["emp_id"].ToString();
lst_emp.DataSource = objds;
lst_emp.DataBind();
|
|
|
|
|
Wrong forum, try posting your question in the C# forum. Also it would be better if you provided a descriptive subject line, rather than 'Hi' or 'Hello' or 'Urgent' etc.
Steve Jowett
-------------------------
It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)
|
|
|
|
|
Hi....
you need to change the below lines...
mon***z wrote: lst_emp.DataTextField=objds.Tables[0].Columns["txtval"].ToString();
lst_emp.DataValueField=objds.Tables[0].Columns["emp_id"].ToString();
to......
lst_emp.DataTextField = "txtval";<br />
lst_emp.DataValueField = "emp_id";
Regards,
Sandeep Kumar.V
|
|
|
|
|
con.Open();<br />
<br />
SqlDataAdapter adp= new SqlDataAdapter(lstrsql,con);<br />
DataSet objds=new DataSet();<br />
adp.Fill(objds); <br />
<br />
lst_emp.DataSource = objds.Tables[0].DefaultView<br />
cmbBookFrom.ValueMember = "emp_id"<br />
cmbBookFrom.DisplayMember = "txtval"
i think this will make your life easier 
|
|
|
|
|
I have the following INSERT command which works well, but I need to limit what is inserted.
INSERT INTO SalesLogix.sysdba.Account SELECT * FROM DONCASTER4.SalesLogix.sysdba.Account
FYI: DONCASTER4 is a linked server.
What I need to do is only insert records where the value of the AccountId column of the source table does not already exist in the destination table.
Both tables are identical and both servers are MS SQL Server 2005.
Your help on this is appreicated.
Steve Jowett
-------------------------
It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)
modified on Wednesday, February 20, 2008 5:45 AM
|
|
|
|
|
User IF NOT EXISTS
for example
IF NOT EXISTS (SELECT * FROM TblA Where AcctId = <<i>value>) INSERT INTO TblA .....
Regards
KP
|
|
|
|
|
Use IF NOT EXISTS
for example ...
IF NOT EXISTS (SELECT * FROM TblA WHERE AcctId = <<i>value>) INSERT INTO TblA .....
Regards
KP
|
|
|
|
|
Thanks for the reply KP, but I do not think you understood my problem which is I have a remote SQL Server (DONCASTER4) on to which new records are inserted into the ACCOUNT table.
Every evening I wish to make a copy of records added to the Accounts table on the DONCASTER4 server onto my master database.
Hope this clarifies what I am trying to achieve.
Regards
Steve Jowett
-------------------------
It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)
|
|
|
|
|
I've got it :-
INSERT INTO SalesLogix.sysdba.ACCOUNT <br />
SELECT * <br />
FROM Doncaster4.salesLogix.sysdba.ACCOUNT AS DONCASTER<br />
WHERE NOT EXISTS(SELECT * FROM SalesLogix.sysdba.ACCOUNT AS HARTSHILL WHERE DONCASTER.ACCOUNTID = HARTSHILL.ACCOUNTID)
Thanks for your help
Steve Jowett
-------------------------
It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)
|
|
|
|
|
One of our project is a content management system something like wikipedia. So I need to calculate the visitors count getting for each article. Each article in this will have a unique id. So I am looking for a best method to keep the visitor count in database.
I have a master table where article details are kept. I tried adding a column named "PageView" which will be updated each time the page is requested. This worked well when website gets very less traffic. But shows incorrect results when it's running on heavy traffic. I guess it's a row level locking issue. Is there any other better approach which can be used to keep the accurate visitor count ?
The work around which is in my mind is, putting a lock to the row when I select "PageView" for updating. Update the count and release the lock. If this is right, will the other select requests to the same row wait until the lock get released ?
|
|
|
|
|
N a v a n e e t h wrote: putting a lock to the row when I select "PageView" for updating. Update the count and release the lock
Why do you select the row and lock it? why don't you just execute a simple update ? also note that in most cases, sql server will handle locks in the best way.
can you post the code you use?
|
|
|
|
|
hspc wrote: Why do you select the row and lock it?
As you know it's a multi threading issue. Database server is multi threaded. So if two requests came at same time, the count may appear incorrect. That's what I thought of using locking.
|
|
|
|
|
Hi,
N a v a n e e t h wrote: I thought of using locking.
Thats a good idea and it will work fine.......I used same concept in one of my projects.
Regards,
Sandeep Kumar.V
|
|
|
|
|
I really believe that you don't need to do so.
this will be safe:
Update tblPages<br />
Set ViewCount = ViwCount + 1<br />
Where PageID = @PageID
this will achieve what you need and will not require any effort on your side to manage locking.
|
|
|
|
|
Hi All;
I make a stored procedure which take ane variable and i use this variable in top clause
here is my procedure
Alter proc SelectTop
@Top_No int
as select top(@Top_No)* from origainal_Msg where status=0
but it not work because i work in sql2000
and when i run sql2005 it not work also
can any body help me to make this procedure in sql2000
thank's all
Kareem Elhosseny
|
|
|
|
|
Sql server 2000 doesn't support variables in TOP clause while sql server 2005 does. That's why you are getting error in sql server 2000.
|
|
|
|
|
I don't think that you can use variables with top clause. Try something like this.
DECLARE @top NVARCHAR(10)
DECLARE @query NVARCHAR(100)
SET @top = '10'
SET @query = 'SELECT TOP ' + @top + ' * FROM YourTable'
EXECUTE SP_EXECUTESQL @query It's not a good practice to form SQL like this.
|
|
|
|
|
thank's my dear but i think it's not high performande to make this
but i have another q i create DB on sql 2000
and try to use sql 2005 for this db
but top query dont work also
Kareem Elhosseny
|
|
|
|
|
As you have been told, you cannot use a variable with TOP in SQL2000. It DOES work in SQL 2005.
See this MS help
Ypur only option for SQL2000 is, as you have already been advised, to create dynamic sql. If this is not performant enough then you will need to look at alternative methods.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Maybey this works for you:
<br />
Alter proc SelectTop<br />
(<br />
@Top_No int <br />
)<br />
as <br />
<br />
set rowcount @top_no<br />
select * from origainal_Msg where status=0<br />
Wout Louwers
|
|
|
|
|
Thank My dear It works
Kareem Elhosseny
|
|
|
|
|
hi all,
I am using sql server 2005.
I making one web form where data from one table(temporary table) is moved to three table two is header and one is detail. i would like to know is making a package is better option or directing writing procedure to move data in respective table is good. Perfomancewise which is fast.or if any other better option is there plaese guide me.
If making ssis packge is better please give me useful.
please help.
Thank you
regards
imran khan
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
