DB Access for SQL Systems

How could I find a list of who has write access within my SQL2K5.
ThanksOn Apr 25, 12:06=A0am, "OA" <omr...@.verizon.net> wrote:
> How could I find =A0a list of who has write access within my SQL2K5.
> Thanks
Hi ,
You can use below commands for your requirement.
Run these procedure under the database for which you need the privs
info.
sp_helprotect
sp_helplogins
sp_helprolemember
Thanks
Ajay Rengunthwar
MCTS,MCDBA,MCAD

days in sql server

Is there any way to find out in sql server that whether it is Monday to friday or saturday sunday.
I have to write a stored procedure in which the logic shoukld be done only if there is a weekend.
if saturday or sunday
select * from status_temp

Please let me know if there is any function that is available in sql

Use DatePart(Weekday, GetDate())

Returns
1 for Sunday
2 for Monday
3 for Tuesday
4 for Wednesday and so on.

|||Hi,
You can also check "SET DATEFIRST" command and @.@.DATEFIRST parameter from BOL.
For US and English Sunday is the first day of week.
Eralper
|||You could also useDATENAME function:

SELECT someColumns FROM status_temp WHERE DATENAME(dw,yourDateTimeColumn) IN ('Saturday','Sunday')

I would guess that DATEPART would be faster, but DATENAME is easier to remember :-)
|||Thanks for all the responses.sql

DateTime string insert into sql datetime column fails

Iam trying to write to a DateTime field in MSSQL from wonderware
intouch. The problem is that I keep getting the error that the string
I'm using is not a valid datetime string....has anybody experienced
this and what was the workaround?
Thanks
GaryThis should arm you with enough information to understand why the operation
fails:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<GaryCharlotte@.Charter.net> wrote in message
news:1145071019.588279.87720@.i39g2000cwa.googlegroups.com...
> Iam trying to write to a DateTime field in MSSQL from wonderware
> intouch. The problem is that I keep getting the error that the string
> I'm using is not a valid datetime string....has anybody experienced
> this and what was the workaround?
> Thanks
> Gary
>|||Thank you Tibor.
I have tried various combinations including the recommended on that
site ie '02/23/1998 14:23:05'
Still no joy. I wonder if this is a wonderware sqlinsert problem...|||> I have tried various combinations including the recommended on that
> site ie '02/23/1998 14:23:05'
That's not recommended, it will fail if, for example, your dateformat is
dmy.
What does "no joy" mean? Does it fail? With what error? Did you try a
safe standard format like
'19980223 14:23:05'
?|||OK I found out what the problem is. If you use a SQLInsertprepare and
SQLInsertexecute it fails no matter what format you use.
Used SQLConnect, SQLInsert and SQLDisconnect and it works great!
Thanks for the help Tiborsql

datetime in in sql query

Hi

I am trying to write a query involve parameters. For example, the query:

Select * from myTable

wheremyDateTime=@.dt;

If I run the query, I was asked to enter value for the parameter. The query can be generated, however I can't save it, the error message says: Must declare the variable @.dt. When I tried to declare it, the system doesn't support it. I am using SQL Server Managerment Studio 2005.

I also tried the query without the parameter:

Select * from myTable

wheremyDateTime=31/07/2007;

But it didn't return record for any datetime format.

Could anyone help please? I just want to get some records filtered by a certain DateTime.

Claire

Are you trying to bulit it as a view or a stored procedure? Its not possible to create a View with paramters.

Stored Proc would look like:

CREATEPROCEDURE sp_MyStoredProc
@.dtasDateTime
AS

BEGIN

SELECT
*
FROM
myTable
WHERE
myDateTime=@.dt

END

To run it you wold have to execute it:

exec sp_MyStoredProc GetDate()

|||

Hi,

You will have to check how are the dates stored in your column. If they are stored as MM/dd/yyyy hh:mm:ss AMPM then you will have to use a Convert function as shown at the end of this post

For your first query, you will need to declare your variable using this

Declare @.dt datetime

Select * from myTable

wheremyDateTime=@.dt;

For your second query, if only the date is stored then

Select * from myTable

wheremyDateTime='31/07/2007'

To understand this better, try these

selectgetdate()

SELECTDATEADD(dd, 0,DATEDIFF(dd, 0,GETDATE()))

SELECTCONVERT(VARCHAR(10),GETDATE(),111)

Check this link

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

DateTime format

Hello all,

I'm trying to write a query against an exisiting table that i can't modify and i'm running into a bit of a problem. The table stores timestamps as a char field instead of a datetime.

So, i've had to use the CONVERT function to change it to a datetime during my query. A sample is below:

SELECT convert(datetime, logged, 120) FROM AP200310

This works, except i want to include the option of querying a single day. Since the data that is returned is in this format:

12/12/2006 6:54:15 PM

The following sql statement doesn't work:
SELECT convert(datetime, logged, 120) FROM AP200310 WHERE logged = '12/12/2006'

Thanks in advance for any help.

Have you tried using cast instead of convert?|||

You need to handle the logged field because it is a datetime field.

You can try this to get your query to work:

SELECT

CONVERT(NVARCHAR(10),logged,120)

FROM

AP200310

WHERE(CONVERT(NVARCHAR(10),logged,101)='12/12/2006')

|||

Thanks for the quick response.

I tried your code and it didn't seem to work.

Help!!

|||

Can you post the results of the query limno posted? and tell us why it does not work. It seems to work for me.

declare @.ttable (col1int identity, col2char(22))insert into @.tvalues ('12/12/2006 6:54:15 PM')insert into @.tvalues ('12/12/2006 6:55:15 PM')insert into @.tvalues ('12/20/2006 6:54:15 PM')insert into @.tvalues ('10/12/2006 6:54:15 PM')selectconvert(nvarchar(10),col2,120), *from @.twhere(CONVERT(NVARCHAR(10),col2,120) ='12/12/2006')

|||

Here is my query.

SELECT CONVERT(NVARCHAR(10), LOGGED, 120) AS LOGGED
FROM AP200612
WHERE (CONVERT(NVARCHAR(10), LOGGED, 101) = '12/12/2006')

My results are nothing is returned.

To help matters, i've included a copy of the schema of the table in question:

CREATETABLE [dbo].[AP200612](

[ACCOUNT] [char]

(9)COLLATE SQL_Latin1_General_CP1_CI_ASNULLCONSTRAINT [gmc_AP200612ACCOUNT]DEFAULT(''),

[LOGGED] [char]

(19)COLLATE SQL_Latin1_General_CP1_CI_ASNULLCONSTRAINT [gmc_AP200612LOGGED]DEFAULT(''),

[ORIGIN] [decimal]

(3, 0)NULLCONSTRAINT [gmc_AP200612ORIGIN]DEFAULT((0)),

[STAT_NUM] [char]

(5)COLLATE SQL_Latin1_General_CP1_CI_ASNULLCONSTRAINT [gmc_AP200612STAT_NUM]DEFAULT(''),

[SUBLOC] [char]

(5)COLLATE SQL_Latin1_General_CP1_CI_ASNULLCONSTRAINT [gmc_AP200612SUBLOC]DEFAULT(''),

[SUFFIX] [char]

(2)COLLATE SQL_Latin1_General_CP1_CI_ASNULLCONSTRAINT [gmc_AP200612SUFFIX]DEFAULT(''),

[TERM] [char]

(5)COLLATE SQL_Latin1_General_CP1_CI_ASNULLCONSTRAINT [gmc_AP200612TERM]DEFAULT(''),

[TIME] [char]

(19)COLLATE SQL_Latin1_General_CP1_CI_ASNULLCONSTRAINT [gmc_AP200612TIME]DEFAULT(''),

[TYPE] [char]

(3)COLLATE SQL_Latin1_General_CP1_CI_ASNULLCONSTRAINT [gmc_AP200612TYPE]DEFAULT('')

)

ON [PRIMARY]

Thanks for your responses and any future responses.

Richard M.

|||

I just replaced your column name and table name and it works for me:

You have different format numbers - 120 and 101, both are same though. Can you also post some sample rows?

declare @.ttable (col1int identity, col2char(19))insert into @.tvalues ('12/12/2006 6:54:15')insert into @.tvalues ('12/12/2006 6:55:15')insert into @.tvalues ('12/20/2006 6:54:15')insert into @.tvalues ('10/12/2006 6:54:15')SELECTCONVERT(NVARCHAR(10), col2, 120)AS col2FROM @.tWHERE (CONVERT(NVARCHAR(10), col2, 101) ='12/12/2006')

|||Hi rmethod, ndinakar's solution should work, I'd like know how you insert rows into the AP200612 table. BTW, if the LOGGED column is used to store some date, why not use DATETIME/SMALLDATETIME data type? Then you can use rich?T-SQLDate and Time functions to filter rows based on the LOGGED column.

DateDiff Display All Fields Begin / End Greater 2 Min

I have a SQL Server 2000 database with the table listed below.
I would like to write sql statement from the table listed below to display
of the
fields in the table using datediff function where begin_time - end_time is
greater than 2 minutes.
Please help me complete this task.
Table City_Time
Name varchar(25),
City varchar(35),
State varchar(2),
Begin_Time datetime,
End_Time datetimeCould it be as simple as one of these?
SELECT *
FROM City_Time
WHERE datediff(second, Begin_Time, End_Time) > 120
or
SELECT *
FROM City_Time
WHERE datediff(minute, Begin_Time, End_Time) > 2
Roy Harvey
Beacon Falls, CT
On Tue, 30 Jan 2007 20:15:01 -0800, Joe K.
<JoeK@.discussions.microsoft.com> wrote:
>I have a SQL Server 2000 database with the table listed below.
>I would like to write sql statement from the table listed below to display
>of the
>fields in the table using datediff function where begin_time - end_time is
>greater than 2 minutes.
>Please help me complete this task.
>Table City_Time
>Name varchar(25),
>City varchar(35),
>State varchar(2),
>Begin_Time datetime,
>End_Time datetime
>

DateDiff Display All Fields Begin / End Greater 2 Min

I have a SQL Server 2000 database with the table listed below.
I would like to write sql statement from the table listed below to display
of the
fields in the table using datediff function where begin_time - end_time is
greater than 2 minutes.
Please help me complete this task.
Table City_Time
Name varchar(25),
City varchar(35),
State varchar(2),
Begin_Time datetime,
End_Time datetime
Could it be as simple as one of these?
SELECT *
FROM City_Time
WHERE datediff(second, Begin_Time, End_Time) > 120
or
SELECT *
FROM City_Time
WHERE datediff(minute, Begin_Time, End_Time) > 2
Roy Harvey
Beacon Falls, CT
On Tue, 30 Jan 2007 20:15:01 -0800, Joe K.
<JoeK@.discussions.microsoft.com> wrote:

>I have a SQL Server 2000 database with the table listed below.
>I would like to write sql statement from the table listed below to display
>of the
>fields in the table using datediff function where begin_time - end_time is
>greater than 2 minutes.
>Please help me complete this task.
>Table City_Time
>Name varchar(25),
>City varchar(35),
>State varchar(2),
>Begin_Time datetime,
>End_Time datetime
>

DateDiff Display All Fields Begin / End Greater 2 Min

I have a SQL Server 2000 database with the table listed below.
I would like to write sql statement from the table listed below to display
of the
fields in the table using datediff function where begin_time - end_time is
greater than 2 minutes.
Please help me complete this task.
Table City_Time
Name varchar(25),
City varchar(35),
State varchar(2),
Begin_Time datetime,
End_Time datetimeCould it be as simple as one of these?
SELECT *
FROM City_Time
WHERE datediff(second, Begin_Time, End_Time) > 120
or
SELECT *
FROM City_Time
WHERE datediff(minute, Begin_Time, End_Time) > 2
Roy Harvey
Beacon Falls, CT
On Tue, 30 Jan 2007 20:15:01 -0800, Joe K.
<JoeK@.discussions.microsoft.com> wrote:

>I have a SQL Server 2000 database with the table listed below.
>I would like to write sql statement from the table listed below to display
>of the
>fields in the table using datediff function where begin_time - end_time is
>greater than 2 minutes.
>Please help me complete this task.
>Table City_Time
>Name varchar(25),
>City varchar(35),
>State varchar(2),
>Begin_Time datetime,
>End_Time datetime
>