How to get todays date in format YY/MM/DD and to compare it to another date passed into th

I need to do the following and am hoping someone can help me out.

I have C#(asp.net app) that will call a stored procedure. The C# will pass in a date to the
stored procedure. The date is in the format YY/MM/DD. Once inside of the stored procedure, the date
passed into the stored proc needs to be compared to todays date. Todays date must be determined in
the SQL.

So basically here is my pseudo code for what I am trying to accomplish. Basically I just am after
the comparison of the two values:

If @.BeginDate < TodaysDate

The difficult part is how to obtain the value for "TodaysDate"

Taking into consideration that "TodaysDate" should probably be in the format of YY/MM/DD considering that is how the date it is to be compared with is being passed in.

Can someone please code this out for me in Microsoft SQL. I would be forever grateful.

I figured out what I needed to know, but will have further questions and will need help. Thanks to all.

How to get the total time of the records in a table

Hello,

I have one table that has a column called CallDuration. This columns has always the format "1/01/2000 12:01:38 AM". The date part "1/01/2000" I want to discard, and sum the time part to get a total time in my query. How can I do that?

Thxselect datediff(hour,'1/01/2000 12:01:38 AM', '1/01/2000 11:10:38 AM') Hours,
datediff(minute,'1/01/2000 12:01:38 AM', '1/01/2000 11:10:38 AM') % 60 Minutes,
datediff(minute,'1/01/2000 12:01:38 AM', '1/01/2000 11:10:38 AM') TotalMinutes|||select datediff(hour,min(callduration), max(callduration)) Hours,
datediff(minute,min(callduration), max(callduration)) % 60 Minutes,
datediff(minute,min(callduration), max(callduration)) TotalMinutes

how to get the top value?

hi,

this my prediction query.

select Flattened (select $time as Date, [Perf] as Perf from PredictTimeSeries([Perf],100) where $Time <'1/1/2007' ) as A from [Stud_Model] WHERE [StudID]=8

Its giving me result where the date is less than 1/1/2007.i need only one result, where the date should be nearer to 1/1/2007. I am not able to use Top 1 inside like

select Flattened (select top 1 $time as Date, [Perf] as Perf from PredictTimeSeries([Perf],100) where $Time <'1/1/2007' ) as A from [Stud_Model] WHERE [StudID]=8

can anyone help.

Thanks,

Karthik

I haven't tried a query like that, but you can't use Top in that context. You may be able to use the TopCount function, I believe the syntax is

TopCount(<table expression>, <count>, <column-ref>) // the last two may be reversed.

TopCount is itself a table expression.

|||

hi jamie,

Its working cool.

Thanks a lot.

How to get the system date and time into database

Hi,

I m using ASP.NET with C#.
I m having one field which should store the datetime of the system.

The datetime should be automatically stored for that entry when the user submits that record on the click event.
Then it should display the date time on the gridview from database.

I m using sqlserver 2005 and i have created the stored procedure for the insert command.

Thisis the sample sp what should be written here to insert system date timeautomatically when the user submits the asp.net form ?

Is there any code for writing directly in stored procedure or asp.net coding page...

ALTER PROCEDURE [dbo].[StoredProcedure1]
@.salesid INT OUTPUT,
@.salesdate datetime,
@.customername varchar(20)
AS
BEGIN
SET NOCOUNT ON
BEGIN
 INSERT INTO sales (customername)
 VALUES (@.customername)
 SELECT @.companyid = SCOPE_IDENTITY()
END
SET NOCOUNT OFF
END

Thanxs in advance...

Hi,

use getdate() function of sql server.

for more info look here

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

|||

You have a choice as to where to use the getdate() function (which returns the current system date and time).

You can actually define it on a column in the table itself so that you don't have to set it programmatically. Here is sample syntax:

create table sales (sales_id int identity, customer_name varchar(30) not null,sales_date datetime default getdate() not null)

Alternatively, within the stored procedure assign your variable with the function call. Here is sample syntax:

select @.SystemDate = getdate()

How to get the record created date?

How do I delete rows that are older than 180 days if the table does not
contain a column of timestamp? The following statement works fine if
parameter startdate is known:
DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
Thanks in advance.
-tcYou don't. Unless you can join with another table that has a datetime
column. (Every table 'should' have such a column: RecordCreated datetime)
And 'timestamp' as the timestamp datatype has nothing to do with TIME.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||SQL Server does not track this information, so you're going to have to add a
column like:
ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
CURRENT_TIMESTAMP;
Of course, you won't be able to take advantage of the values for this
specifc task until 6 more months have passed.
A
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||> (Every table 'should' have such a column: RecordCreated datetime)
Whoa, that's a pretty broad generalization. Such a column is useful, but in
most applications it isn't necessary on every single table.
A|||Very True, but I prefer to drive home the point for the generalization, and
then once the idea has taken hold, start dealing with the exceptions. And of
course, not needed at all in some databases.
But for general business data ...
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
> Whoa, that's a pretty broad generalization. Such a column is useful, but
> in most applications it isn't necessary on every single table.
> A
>|||It would be better to drive home the fact that each row should take up as
little space as possible and then deal with the exceptions. That way you
encourage efficiency.
Best regards
Mark Baldwin
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Very True, but I prefer to drive home the point for the generalization,
> and then once the idea has taken hold, start dealing with the exceptions.
> And of course, not needed at all in some databases.
> But for general business data ...
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>|||I'm less concerned with saving 4 or 8 bytes than with having a 'robust' data
system -I keep hearing storage is inexpensive. Perhaps it's just the segment
of large corporation and government agencies that I work with, but for the
last several years, all projects I've worked with have either required or
benefited from having not only a date column (entered/changed) -but also a
column capturing 'who' was responsible. And updates/deletes from selected
tables may be shadowed to an archive table/server. I tend to work with
VLDB's that must meet requirements set out by Aegis (law enforcement),
SarBox (financial), and/or HIPPA (medical).
Personally, I think that business systems 'should' have date and person
columns on almost every table. They solve so many of the 'problems' that
come up over a project's lifecycle. -Such as the one posted by the OP.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Mark" <swozz_@.hotmail.com> wrote in message
news:OT$O7e$pGHA.5064@.TK2MSFTNGP05.phx.gbl...
> It would be better to drive home the fact that each row should take up as
> little space as possible and then deal with the exceptions. That way you
> encourage efficiency.
> --
> Best regards
> Mark Baldwin
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>|||> benefited from having not only a date column (entered/changed) -but also
> a column capturing 'who' was responsible.
That kind of data, imho, belongs in a separate auditing table.
Otherwise, you are forced to either have really wide tables or only track
the LAST person who changed it.

> Personally, I think that business systems 'should' have date and person
> columns on almost every table.
I think for higher level entities that's not a bad idea. For complex OLTP
systems with hundreds of tables, you're going to drown yourself in
information overload.
A|||Thank you very much, guys. I think I will add a timestamp column to my table
next time.
-tc
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> SQL Server does not track this information, so you're going to have to add
> a column like:
> ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> CURRENT_TIMESTAMP;
> Of course, you won't be able to take advantage of the values for this
> specifc task until 6 more months have passed.
> A
>
> "tcw" <tcwangs@.msn.com> wrote in message
> news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>|||"tcw" <tcwangs@.msn.com> wrote in message
news:egpsl9GqGHA.4932@.TK2MSFTNGP05.phx.gbl...
> Thank you very much, guys. I think I will add a timestamp column to my
table
> next time.
Note: To be perfectly clear, you wanta datetime (or smalldatetime) column.
Timestamp is a separate datatype which actually doesn't map to date or time.

> -tc
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message
> news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
add[vbcol=seagreen]
>

How to get the record created date?

How do I delete rows that are older than 180 days if the table does not
contain a column of timestamp? The following statement works fine if
parameter startdate is known:
DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
Thanks in advance.
-tcYou don't. Unless you can join with another table that has a datetime
column. (Every table 'should' have such a column: RecordCreated datetime)
And 'timestamp' as the timestamp datatype has nothing to do with TIME.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||SQL Server does not track this information, so you're going to have to add a
column like:
ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
CURRENT_TIMESTAMP;
Of course, you won't be able to take advantage of the values for this
specifc task until 6 more months have passed.
A
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||> (Every table 'should' have such a column: RecordCreated datetime)
Whoa, that's a pretty broad generalization. Such a column is useful, but in
most applications it isn't necessary on every single table.
A|||Very True, but I prefer to drive home the point for the generalization, and
then once the idea has taken hold, start dealing with the exceptions. And of
course, not needed at all in some databases.
But for general business data ...
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
> Whoa, that's a pretty broad generalization. Such a column is useful, but
> in most applications it isn't necessary on every single table.
> A
>|||It would be better to drive home the fact that each row should take up as
little space as possible and then deal with the exceptions. That way you
encourage efficiency.
--
Best regards
Mark Baldwin
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Very True, but I prefer to drive home the point for the generalization,
> and then once the idea has taken hold, start dealing with the exceptions.
> And of course, not needed at all in some databases.
> But for general business data ...
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
>> Whoa, that's a pretty broad generalization. Such a column is useful, but
>> in most applications it isn't necessary on every single table.
>> A
>|||I'm less concerned with saving 4 or 8 bytes than with having a 'robust' data
system -I keep hearing storage is inexpensive. Perhaps it's just the segment
of large corporation and government agencies that I work with, but for the
last several years, all projects I've worked with have either required or
benefited from having not only a date column (entered/changed) -but also a
column capturing 'who' was responsible. And updates/deletes from selected
tables may be shadowed to an archive table/server. I tend to work with
VLDB's that must meet requirements set out by Aegis (law enforcement),
SarBox (financial), and/or HIPPA (medical).
Personally, I think that business systems 'should' have date and person
columns on almost every table. They solve so many of the 'problems' that
come up over a project's lifecycle. -Such as the one posted by the OP.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Mark" <swozz_@.hotmail.com> wrote in message
news:OT$O7e$pGHA.5064@.TK2MSFTNGP05.phx.gbl...
> It would be better to drive home the fact that each row should take up as
> little space as possible and then deal with the exceptions. That way you
> encourage efficiency.
> --
> Best regards
> Mark Baldwin
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>> Very True, but I prefer to drive home the point for the generalization,
>> and then once the idea has taken hold, start dealing with the exceptions.
>> And of course, not needed at all in some databases.
>> But for general business data ...
>> --
>> Arnie Rowland
>> "To be successful, your heart must accompany your knowledge."
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
>> Whoa, that's a pretty broad generalization. Such a column is useful,
>> but in most applications it isn't necessary on every single table.
>> A
>>
>|||> benefited from having not only a date column (entered/changed) -but also
> a column capturing 'who' was responsible.
That kind of data, imho, belongs in a separate auditing table.
Otherwise, you are forced to either have really wide tables or only track
the LAST person who changed it.
> Personally, I think that business systems 'should' have date and person
> columns on almost every table.
I think for higher level entities that's not a bad idea. For complex OLTP
systems with hundreds of tables, you're going to drown yourself in
information overload.
A|||Thank you very much, guys. I think I will add a timestamp column to my table
next time.
-tc
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> SQL Server does not track this information, so you're going to have to add
> a column like:
> ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> CURRENT_TIMESTAMP;
> Of course, you won't be able to take advantage of the values for this
> specifc task until 6 more months have passed.
> A
>
> "tcw" <tcwangs@.msn.com> wrote in message
> news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>> How do I delete rows that are older than 180 days if the table does not
>> contain a column of timestamp? The following statement works fine if
>> parameter startdate is known:
>> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
>> Thanks in advance.
>> -tc
>|||"tcw" <tcwangs@.msn.com> wrote in message
news:egpsl9GqGHA.4932@.TK2MSFTNGP05.phx.gbl...
> Thank you very much, guys. I think I will add a timestamp column to my
table
> next time.
Note: To be perfectly clear, you wanta datetime (or smalldatetime) column.
Timestamp is a separate datatype which actually doesn't map to date or time.
> -tc
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message
> news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> > SQL Server does not track this information, so you're going to have to
add
> > a column like:
> >
> > ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> > CURRENT_TIMESTAMP;
> >
> > Of course, you won't be able to take advantage of the values for this
> > specifc task until 6 more months have passed.
> >
> > A
> >
> >
> > "tcw" <tcwangs@.msn.com> wrote in message
> > news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> >> How do I delete rows that are older than 180 days if the table does not
> >> contain a column of timestamp? The following statement works fine if
> >> parameter startdate is known:
> >>
> >> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> >>
> >> Thanks in advance.
> >>
> >> -tc
> >>
> >
> >
>sql

How to get the record created date?

How do I delete rows that are older than 180 days if the table does not
contain a column of timestamp? The following statement works fine if
parameter startdate is known:
DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
Thanks in advance.
-tcYou don't. Unless you can join with another table that has a datetime
column. (Every table 'should' have such a column: RecordCreated datetime)
And 'timestamp' as the timestamp datatype has nothing to do with TIME.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||SQL Server does not track this information, so you're going to have to add a
column like:
ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
CURRENT_TIMESTAMP;
Of course, you won't be able to take advantage of the values for this
specifc task until 6 more months have passed.
A
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||> (Every table 'should' have such a column: RecordCreated datetime)
Whoa, that's a pretty broad generalization. Such a column is useful, but in
most applications it isn't necessary on every single table.
A|||Very True, but I prefer to drive home the point for the generalization, and
then once the idea has taken hold, start dealing with the exceptions. And of
course, not needed at all in some databases.
But for general business data ...
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
> Whoa, that's a pretty broad generalization. Such a column is useful, but
> in most applications it isn't necessary on every single table.
> A
>|||It would be better to drive home the fact that each row should take up as
little space as possible and then deal with the exceptions. That way you
encourage efficiency.
--
Best regards
Mark Baldwin
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Very True, but I prefer to drive home the point for the generalization,
> and then once the idea has taken hold, start dealing with the exceptions.
> And of course, not needed at all in some databases.
> But for general business data ...
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
>> Whoa, that's a pretty broad generalization. Such a column is useful, but
>> in most applications it isn't necessary on every single table.
>> A
>|||I'm less concerned with saving 4 or 8 bytes than with having a 'robust' data
system -I keep hearing storage is inexpensive. Perhaps it's just the segment
of large corporation and government agencies that I work with, but for the
last several years, all projects I've worked with have either required or
benefited from having not only a date column (entered/changed) -but also a
column capturing 'who' was responsible. And updates/deletes from selected
tables may be shadowed to an archive table/server. I tend to work with
VLDB's that must meet requirements set out by Aegis (law enforcement),
SarBox (financial), and/or HIPPA (medical).
Personally, I think that business systems 'should' have date and person
columns on almost every table. They solve so many of the 'problems' that
come up over a project's lifecycle. -Such as the one posted by the OP.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Mark" <swozz_@.hotmail.com> wrote in message
news:OT$O7e$pGHA.5064@.TK2MSFTNGP05.phx.gbl...
> It would be better to drive home the fact that each row should take up as
> little space as possible and then deal with the exceptions. That way you
> encourage efficiency.
> --
> Best regards
> Mark Baldwin
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>> Very True, but I prefer to drive home the point for the generalization,
>> and then once the idea has taken hold, start dealing with the exceptions.
>> And of course, not needed at all in some databases.
>> But for general business data ...
>> --
>> Arnie Rowland
>> "To be successful, your heart must accompany your knowledge."
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
>> Whoa, that's a pretty broad generalization. Such a column is useful,
>> but in most applications it isn't necessary on every single table.
>> A
>>
>|||> benefited from having not only a date column (entered/changed) -but also
> a column capturing 'who' was responsible.
That kind of data, imho, belongs in a separate auditing table.
Otherwise, you are forced to either have really wide tables or only track
the LAST person who changed it.
> Personally, I think that business systems 'should' have date and person
> columns on almost every table.
I think for higher level entities that's not a bad idea. For complex OLTP
systems with hundreds of tables, you're going to drown yourself in
information overload.
A|||Thank you very much, guys. I think I will add a timestamp column to my table
next time.
-tc
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> SQL Server does not track this information, so you're going to have to add
> a column like:
> ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> CURRENT_TIMESTAMP;
> Of course, you won't be able to take advantage of the values for this
> specifc task until 6 more months have passed.
> A
>
> "tcw" <tcwangs@.msn.com> wrote in message
> news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>> How do I delete rows that are older than 180 days if the table does not
>> contain a column of timestamp? The following statement works fine if
>> parameter startdate is known:
>> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
>> Thanks in advance.
>> -tc
>|||"tcw" <tcwangs@.msn.com> wrote in message
news:egpsl9GqGHA.4932@.TK2MSFTNGP05.phx.gbl...
> Thank you very much, guys. I think I will add a timestamp column to my
table
> next time.
Note: To be perfectly clear, you wanta datetime (or smalldatetime) column.
Timestamp is a separate datatype which actually doesn't map to date or time.
> -tc
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message
> news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> > SQL Server does not track this information, so you're going to have to
add
> > a column like:
> >
> > ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> > CURRENT_TIMESTAMP;
> >
> > Of course, you won't be able to take advantage of the values for this
> > specifc task until 6 more months have passed.
> >
> > A
> >
> >
> > "tcw" <tcwangs@.msn.com> wrote in message
> > news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> >> How do I delete rows that are older than 180 days if the table does not
> >> contain a column of timestamp? The following statement works fine if
> >> parameter startdate is known:
> >>
> >> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> >>
> >> Thanks in advance.
> >>
> >> -tc
> >>
> >
> >
>

How to get the password change date in SQL Server

I have searched this topic on google first, and I understood that "xdate2
column of sysxlogins" may store this info, but might not exclusively store
this info... this value changes as extra permissions is granted.
Is there a way to get the password change date? Either within a column of a
table, or programatically?
Thank you in advance.
/SMAC
Actually if you are using SQL Server 2005 you can use
select name,modify_date from sys.server_principals
However ,there is no guarantee that only password has been changed , could
be name as well
"SMAC" <info@.smactool.com> wrote in message
news:rVsQg.340$gE7.320@.newsfe05.lga...
>I have searched this topic on google first, and I understood that "xdate2
>column of sysxlogins" may store this info, but might not exclusively store
>this info... this value changes as extra permissions is granted.
> Is there a way to get the password change date? Either within a column of
> a table, or programatically?
> Thank you in advance.
> /
>|||Thank you Uri,
Any sure way to do this for all SQL 7, 2000, 2005?
Thanks,
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uj94vFW3GHA.4648@.TK2MSFTNGP04.phx.gbl...
> SMAC
> Actually if you are using SQL Server 2005 you can use
> select name,modify_date from sys.server_principals
>
> However ,there is no guarantee that only password has been changed , could
> be name as well
>
>
>
> "SMAC" <info@.smactool.com> wrote in message
> news:rVsQg.340$gE7.320@.newsfe05.lga...
>|||Check out the syslogins table (view on 2005).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SMAC" <info@.smactool.com> wrote in message news:j2wQg.5$v14.4@.newsfe02.lga...n">
> Thank you Uri,
> Any sure way to do this for all SQL 7, 2000, 2005?
> Thanks,
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uj94vFW3GHA.4648@.TK2MSFTNGP04.phx.gbl...
>|||In 2005 there is loginproperties I can use, how about SQL 7 and 2000? Any
idea?
Thank you.
/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u9WqBDb3GHA.4560@.TK2MSFTNGP05.phx.gbl...[vbcol=seagreen]
> Check out the syslogins table (view on 2005).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SMAC" <info@.smactool.com> wrote in message
> news:j2wQg.5$v14.4@.newsfe02.lga...|||No, that function doesn't exist in earlier versions. I'd go for syslogins on
pre-2005 and the
LOGINPROPERTY function on 2005 (version check in your code).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SMAC" <info@.smactool.com> wrote in message news:rSIQg.73$c86.46@.newsfe07.lga...een">
> In 2005 there is loginproperties I can use, how about SQL 7 and 2000? Any
idea?
> Thank you.
> /
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:u9WqBDb3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>|||Thanks Tibor,
syslogins doesn't give the reliable password modified date because any
changes to the logins will change the date within syslogins / sysxlogins...
Maybe I'll ask this question in a different way...
when CHECK_EXPIRATION property of the login is set to ON, how does SQL
Server check the password expiration? I believe there is a place where it
stores password creation date right?
Please share your insight.
Thanks!
/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:elCVDFh3GHA.3492@.TK2MSFTNGP06.phx.gbl...
> No, that function doesn't exist in earlier versions. I'd go for syslogins
> on pre-2005 and the LOGINPROPERTY function on 2005 (version check in your
> code).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SMAC" <info@.smactool.com> wrote in message
> news:rSIQg.73$c86.46@.newsfe07.lga...
>|||Most probably you cannot differentiate between password change and other log
in modifications in 2000
and earlier. So for those versions, syslogins is what you have.
As for 2005, where is the problem? Why not use the LOGINPROPERTY() function,
which you already found
and according to the documentation can answer when password was last changed
? Btw, it seems like the
information from that function comes from the same place as the modified_dat
e in sys.sql_logins (as
this doesn't change when I alter the default database for a login...).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SMAC" <info@.smactool.com> wrote in message news:6dQQg.310$aF4.164@.newsfe06.lga...green">
> Thanks Tibor,
> syslogins doesn't give the reliable password modified date because any cha
nges to the logins will
> change the date within syslogins / sysxlogins...
> Maybe I'll ask this question in a different way...
> when CHECK_EXPIRATION property of the login is set to ON, how does SQL Ser
ver check the password
> expiration? I believe there is a place where it stores password creation
date right?
> Please share your insight.
> Thanks!
> /
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:elCVDFh3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>|||I believe this is the field that you are looking for:
select xdate2 AS password_change_date,
DATEDIFF(dd, xdate2, GETDATE()) AS days_since_change
FROM master.dbo.sysxlogins
Tibor Karaszi wrote:[vbcol=seagreen]
> Most probably you cannot differentiate between password change and other l
ogin modifications in 2000
> and earlier. So for those versions, syslogins is what you have.
> As for 2005, where is the problem? Why not use the LOGINPROPERTY() functio
n, which you already found
> and according to the documentation can answer when password was last chang
ed? Btw, it seems like the
> information from that function comes from the same place as the modified_d
ate in sys.sql_logins (as
> this doesn't change when I alter the default database for a login...).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SMAC" <info@.smactool.com> wrote in message news:6dQQg.310$aF4.164@.newsfe0
6.lga...sql

How to get the password change date in SQL Server

I have searched this topic on google first, and I understood that "xdate2
column of sysxlogins" may store this info, but might not exclusively store
this info... this value changes as extra permissions is granted.
Is there a way to get the password change date? Either within a column of a
table, or programatically?
Thank you in advance.
/
SMAC
Actually if you are using SQL Server 2005 you can use
select name,modify_date from sys.server_principals
However ,there is no guarantee that only password has been changed , could
be name as well
"SMAC" <info@.smactool.com> wrote in message
news:rVsQg.340$gE7.320@.newsfe05.lga...
>I have searched this topic on google first, and I understood that "xdate2
>column of sysxlogins" may store this info, but might not exclusively store
>this info... this value changes as extra permissions is granted.
> Is there a way to get the password change date? Either within a column of
> a table, or programatically?
> Thank you in advance.
> /
>
|||Thank you Uri,
Any sure way to do this for all SQL 7, 2000, 2005?
Thanks,
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uj94vFW3GHA.4648@.TK2MSFTNGP04.phx.gbl...
> SMAC
> Actually if you are using SQL Server 2005 you can use
> select name,modify_date from sys.server_principals
>
> However ,there is no guarantee that only password has been changed , could
> be name as well
>
>
>
> "SMAC" <info@.smactool.com> wrote in message
> news:rVsQg.340$gE7.320@.newsfe05.lga...
>
|||Check out the syslogins table (view on 2005).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SMAC" <info@.smactool.com> wrote in message news:j2wQg.5$v14.4@.newsfe02.lga...
> Thank you Uri,
> Any sure way to do this for all SQL 7, 2000, 2005?
> Thanks,
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uj94vFW3GHA.4648@.TK2MSFTNGP04.phx.gbl...
>
|||In 2005 there is loginproperties I can use, how about SQL 7 and 2000? Any
idea?
Thank you.
/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u9WqBDb3GHA.4560@.TK2MSFTNGP05.phx.gbl...[vbcol=seagreen]
> Check out the syslogins table (view on 2005).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SMAC" <info@.smactool.com> wrote in message
> news:j2wQg.5$v14.4@.newsfe02.lga...
|||No, that function doesn't exist in earlier versions. I'd go for syslogins on pre-2005 and the
LOGINPROPERTY function on 2005 (version check in your code).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SMAC" <info@.smactool.com> wrote in message news:rSIQg.73$c86.46@.newsfe07.lga...
> In 2005 there is loginproperties I can use, how about SQL 7 and 2000? Any idea?
> Thank you.
> /
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:u9WqBDb3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>
|||Thanks Tibor,
syslogins doesn't give the reliable password modified date because any
changes to the logins will change the date within syslogins / sysxlogins...
Maybe I'll ask this question in a different way...
when CHECK_EXPIRATION property of the login is set to ON, how does SQL
Server check the password expiration? I believe there is a place where it
stores password creation date right?
Please share your insight.
Thanks!
/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:elCVDFh3GHA.3492@.TK2MSFTNGP06.phx.gbl...
> No, that function doesn't exist in earlier versions. I'd go for syslogins
> on pre-2005 and the LOGINPROPERTY function on 2005 (version check in your
> code).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SMAC" <info@.smactool.com> wrote in message
> news:rSIQg.73$c86.46@.newsfe07.lga...
>
|||Most probably you cannot differentiate between password change and other login modifications in 2000
and earlier. So for those versions, syslogins is what you have.
As for 2005, where is the problem? Why not use the LOGINPROPERTY() function, which you already found
and according to the documentation can answer when password was last changed? Btw, it seems like the
information from that function comes from the same place as the modified_date in sys.sql_logins (as
this doesn't change when I alter the default database for a login...).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SMAC" <info@.smactool.com> wrote in message news:6dQQg.310$aF4.164@.newsfe06.lga...
> Thanks Tibor,
> syslogins doesn't give the reliable password modified date because any changes to the logins will
> change the date within syslogins / sysxlogins...
> Maybe I'll ask this question in a different way...
> when CHECK_EXPIRATION property of the login is set to ON, how does SQL Server check the password
> expiration? I believe there is a place where it stores password creation date right?
> Please share your insight.
> Thanks!
> /
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:elCVDFh3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>
|||I believe this is the field that you are looking for:
selectxdate2 AS password_change_date,
DATEDIFF(dd, xdate2, GETDATE()) AS days_since_change
FROMmaster.dbo.sysxlogins
Tibor Karaszi wrote:[vbcol=seagreen]
> Most probably you cannot differentiate between password change and other login modifications in 2000
> and earlier. So for those versions, syslogins is what you have.
> As for 2005, where is the problem? Why not use the LOGINPROPERTY() function, which you already found
> and according to the documentation can answer when password was last changed? Btw, it seems like the
> information from that function comes from the same place as the modified_date in sys.sql_logins (as
> this doesn't change when I alter the default database for a login...).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SMAC" <info@.smactool.com> wrote in message news:6dQQg.310$aF4.164@.newsfe06.lga...

How to get the password change date in SQL Server

I have searched this topic on google first, and I understood that "xdate2
column of sysxlogins" may store this info, but might not exclusively store
this info... this value changes as extra permissions is granted.
Is there a way to get the password change date? Either within a column of a
table, or programatically?
Thank you in advance.
/SMAC
Actually if you are using SQL Server 2005 you can use
select name,modify_date from sys.server_principals
However ,there is no guarantee that only password has been changed , could
be name as well
"SMAC" <info@.smactool.com> wrote in message
news:rVsQg.340$gE7.320@.newsfe05.lga...
>I have searched this topic on google first, and I understood that "xdate2
>column of sysxlogins" may store this info, but might not exclusively store
>this info... this value changes as extra permissions is granted.
> Is there a way to get the password change date? Either within a column of
> a table, or programatically?
> Thank you in advance.
> /
>|||Thank you Uri,
Any sure way to do this for all SQL 7, 2000, 2005?
Thanks,
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uj94vFW3GHA.4648@.TK2MSFTNGP04.phx.gbl...
> SMAC
> Actually if you are using SQL Server 2005 you can use
> select name,modify_date from sys.server_principals
>
> However ,there is no guarantee that only password has been changed , could
> be name as well
>
>
>
> "SMAC" <info@.smactool.com> wrote in message
> news:rVsQg.340$gE7.320@.newsfe05.lga...
>>I have searched this topic on google first, and I understood that "xdate2
>>column of sysxlogins" may store this info, but might not exclusively store
>>this info... this value changes as extra permissions is granted.
>> Is there a way to get the password change date? Either within a column
>> of a table, or programatically?
>> Thank you in advance.
>> /
>|||Check out the syslogins table (view on 2005).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SMAC" <info@.smactool.com> wrote in message news:j2wQg.5$v14.4@.newsfe02.lga...
> Thank you Uri,
> Any sure way to do this for all SQL 7, 2000, 2005?
> Thanks,
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uj94vFW3GHA.4648@.TK2MSFTNGP04.phx.gbl...
>> SMAC
>> Actually if you are using SQL Server 2005 you can use
>> select name,modify_date from sys.server_principals
>>
>> However ,there is no guarantee that only password has been changed , could
>> be name as well
>>
>>
>>
>> "SMAC" <info@.smactool.com> wrote in message
>> news:rVsQg.340$gE7.320@.newsfe05.lga...
>>I have searched this topic on google first, and I understood that "xdate2
>>column of sysxlogins" may store this info, but might not exclusively store
>>this info... this value changes as extra permissions is granted.
>> Is there a way to get the password change date? Either within a column
>> of a table, or programatically?
>> Thank you in advance.
>> /
>>
>|||In 2005 there is loginproperties I can use, how about SQL 7 and 2000? Any
idea?
Thank you.
/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u9WqBDb3GHA.4560@.TK2MSFTNGP05.phx.gbl...
> Check out the syslogins table (view on 2005).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SMAC" <info@.smactool.com> wrote in message
> news:j2wQg.5$v14.4@.newsfe02.lga...
>> Thank you Uri,
>> Any sure way to do this for all SQL 7, 2000, 2005?
>> Thanks,
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uj94vFW3GHA.4648@.TK2MSFTNGP04.phx.gbl...
>> SMAC
>> Actually if you are using SQL Server 2005 you can use
>> select name,modify_date from sys.server_principals
>>
>> However ,there is no guarantee that only password has been changed ,
>> could be name as well
>>
>>
>>
>> "SMAC" <info@.smactool.com> wrote in message
>> news:rVsQg.340$gE7.320@.newsfe05.lga...
>>I have searched this topic on google first, and I understood that
>>"xdate2 column of sysxlogins" may store this info, but might not
>>exclusively store this info... this value changes as extra permissions
>>is granted.
>> Is there a way to get the password change date? Either within a column
>> of a table, or programatically?
>> Thank you in advance.
>> /
>>
>>|||No, that function doesn't exist in earlier versions. I'd go for syslogins on pre-2005 and the
LOGINPROPERTY function on 2005 (version check in your code).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SMAC" <info@.smactool.com> wrote in message news:rSIQg.73$c86.46@.newsfe07.lga...
> In 2005 there is loginproperties I can use, how about SQL 7 and 2000? Any idea?
> Thank you.
> /
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:u9WqBDb3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>> Check out the syslogins table (view on 2005).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "SMAC" <info@.smactool.com> wrote in message news:j2wQg.5$v14.4@.newsfe02.lga...
>> Thank you Uri,
>> Any sure way to do this for all SQL 7, 2000, 2005?
>> Thanks,
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:uj94vFW3GHA.4648@.TK2MSFTNGP04.phx.gbl...
>> SMAC
>> Actually if you are using SQL Server 2005 you can use
>> select name,modify_date from sys.server_principals
>>
>> However ,there is no guarantee that only password has been changed , could be name as well
>>
>>
>>
>> "SMAC" <info@.smactool.com> wrote in message news:rVsQg.340$gE7.320@.newsfe05.lga...
>>I have searched this topic on google first, and I understood that "xdate2 column of sysxlogins"
>>may store this info, but might not exclusively store this info... this value changes as extra
>>permissions is granted.
>> Is there a way to get the password change date? Either within a column of a table, or
>> programatically?
>> Thank you in advance.
>> /
>>
>>
>|||Thanks Tibor,
syslogins doesn't give the reliable password modified date because any
changes to the logins will change the date within syslogins / sysxlogins...
Maybe I'll ask this question in a different way...
when CHECK_EXPIRATION property of the login is set to ON, how does SQL
Server check the password expiration? I believe there is a place where it
stores password creation date right?
Please share your insight.
Thanks!
/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:elCVDFh3GHA.3492@.TK2MSFTNGP06.phx.gbl...
> No, that function doesn't exist in earlier versions. I'd go for syslogins
> on pre-2005 and the LOGINPROPERTY function on 2005 (version check in your
> code).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SMAC" <info@.smactool.com> wrote in message
> news:rSIQg.73$c86.46@.newsfe07.lga...
>> In 2005 there is loginproperties I can use, how about SQL 7 and 2000?
>> Any idea?
>> Thank you.
>> /
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:u9WqBDb3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>> Check out the syslogins table (view on 2005).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "SMAC" <info@.smactool.com> wrote in message
>> news:j2wQg.5$v14.4@.newsfe02.lga...
>> Thank you Uri,
>> Any sure way to do this for all SQL 7, 2000, 2005?
>> Thanks,
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uj94vFW3GHA.4648@.TK2MSFTNGP04.phx.gbl...
>> SMAC
>> Actually if you are using SQL Server 2005 you can use
>> select name,modify_date from sys.server_principals
>>
>> However ,there is no guarantee that only password has been changed ,
>> could be name as well
>>
>>
>>
>> "SMAC" <info@.smactool.com> wrote in message
>> news:rVsQg.340$gE7.320@.newsfe05.lga...
>>I have searched this topic on google first, and I understood that
>>"xdate2 column of sysxlogins" may store this info, but might not
>>exclusively store this info... this value changes as extra permissions
>>is granted.
>> Is there a way to get the password change date? Either within a
>> column of a table, or programatically?
>> Thank you in advance.
>> /
>>
>>
>|||Most probably you cannot differentiate between password change and other login modifications in 2000
and earlier. So for those versions, syslogins is what you have.
As for 2005, where is the problem? Why not use the LOGINPROPERTY() function, which you already found
and according to the documentation can answer when password was last changed? Btw, it seems like the
information from that function comes from the same place as the modified_date in sys.sql_logins (as
this doesn't change when I alter the default database for a login...).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SMAC" <info@.smactool.com> wrote in message news:6dQQg.310$aF4.164@.newsfe06.lga...
> Thanks Tibor,
> syslogins doesn't give the reliable password modified date because any changes to the logins will
> change the date within syslogins / sysxlogins...
> Maybe I'll ask this question in a different way...
> when CHECK_EXPIRATION property of the login is set to ON, how does SQL Server check the password
> expiration? I believe there is a place where it stores password creation date right?
> Please share your insight.
> Thanks!
> /
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:elCVDFh3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>> No, that function doesn't exist in earlier versions. I'd go for syslogins on pre-2005 and the
>> LOGINPROPERTY function on 2005 (version check in your code).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "SMAC" <info@.smactool.com> wrote in message news:rSIQg.73$c86.46@.newsfe07.lga...
>> In 2005 there is loginproperties I can use, how about SQL 7 and 2000? Any idea?
>> Thank you.
>> /
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:u9WqBDb3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>> Check out the syslogins table (view on 2005).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "SMAC" <info@.smactool.com> wrote in message news:j2wQg.5$v14.4@.newsfe02.lga...
>> Thank you Uri,
>> Any sure way to do this for all SQL 7, 2000, 2005?
>> Thanks,
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:uj94vFW3GHA.4648@.TK2MSFTNGP04.phx.gbl...
>> SMAC
>> Actually if you are using SQL Server 2005 you can use
>> select name,modify_date from sys.server_principals
>>
>> However ,there is no guarantee that only password has been changed , could be name as well
>>
>>
>>
>> "SMAC" <info@.smactool.com> wrote in message news:rVsQg.340$gE7.320@.newsfe05.lga...
>>>I have searched this topic on google first, and I understood that "xdate2 column of
>>>sysxlogins" may store this info, but might not exclusively store this info... this value
>>>changes as extra permissions is granted.
>>>
>>> Is there a way to get the password change date? Either within a column of a table, or
>>> programatically?
>>>
>>> Thank you in advance.
>>> /
>>>
>>
>>
>|||I believe this is the field that you are looking for:
select xdate2 AS password_change_date,
DATEDIFF(dd, xdate2, GETDATE()) AS days_since_change
FROM master.dbo.sysxlogins
Tibor Karaszi wrote:
> Most probably you cannot differentiate between password change and other login modifications in 2000
> and earlier. So for those versions, syslogins is what you have.
> As for 2005, where is the problem? Why not use the LOGINPROPERTY() function, which you already found
> and according to the documentation can answer when password was last changed? Btw, it seems like the
> information from that function comes from the same place as the modified_date in sys.sql_logins (as
> this doesn't change when I alter the default database for a login...).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SMAC" <info@.smactool.com> wrote in message news:6dQQg.310$aF4.164@.newsfe06.lga...
> > Thanks Tibor,
> >
> > syslogins doesn't give the reliable password modified date because any changes to the logins will
> > change the date within syslogins / sysxlogins...
> >
> > Maybe I'll ask this question in a different way...
> > when CHECK_EXPIRATION property of the login is set to ON, how does SQL Server check the password
> > expiration? I believe there is a place where it stores password creation date right?
> >
> > Please share your insight.
> >
> > Thanks!
> > /
> >
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> > news:elCVDFh3GHA.3492@.TK2MSFTNGP06.phx.gbl...
> >> No, that function doesn't exist in earlier versions. I'd go for syslogins on pre-2005 and the
> >> LOGINPROPERTY function on 2005 (version check in your code).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "SMAC" <info@.smactool.com> wrote in message news:rSIQg.73$c86.46@.newsfe07.lga...
> >> In 2005 there is loginproperties I can use, how about SQL 7 and 2000? Any idea?
> >>
> >> Thank you.
> >> /
> >>
> >> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> >> news:u9WqBDb3GHA.4560@.TK2MSFTNGP05.phx.gbl...
> >> Check out the syslogins table (view on 2005).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "SMAC" <info@.smactool.com> wrote in message news:j2wQg.5$v14.4@.newsfe02.lga...
> >> Thank you Uri,
> >>
> >> Any sure way to do this for all SQL 7, 2000, 2005?
> >>
> >> Thanks,
> >>
> >>
> >> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:uj94vFW3GHA.4648@.TK2MSFTNGP04.phx.gbl...
> >> SMAC
> >> Actually if you are using SQL Server 2005 you can use
> >> select name,modify_date from sys.server_principals
> >>
> >>
> >>
> >> However ,there is no guarantee that only password has been changed , could be name as well
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> "SMAC" <info@.smactool.com> wrote in message news:rVsQg.340$gE7.320@.newsfe05.lga...
> >>>I have searched this topic on google first, and I understood that "xdate2 column of
> >>>sysxlogins" may store this info, but might not exclusively store this info... this value
> >>>changes as extra permissions is granted.
> >>>
> >>> Is there a way to get the password change date? Either within a column of a table, or
> >>> programatically?
> >>>
> >>> Thank you in advance.
> >>> /
> >>>
> >>
> >>
> >>
> >>
> >>
> >
> >

How to get the latest & earliest date?

Hi,

How to get the latest date from ColumnDateTime?

and the earlier as well ?

select max(columnDateTime), -- for lastest date
min(columnDateTime) -- for earliest date|||Thanks a lot :)|||what if I want to use it with "WHERE" Condition ?|||

Maybe something like

select max(columnDateTime)
from yourSourceTable
where anIntegerColumn = 14

If your column is a varchar column that WHERE condition will look like:

where aVarcharColumn = 'theTargetString'

How to get the first date

I have a time dimention of Year, Month and Date
I want to know the unit sales of the first date of every month of every
store.
How can I implement the MDX?
look at this:
http://msdn.microsoft.com/library/de...l/semiadd2.asp
(Semi additive measures)
You are in the same case, except you want the start date instead-of the end
date in your calculations.
"ad" <ad@.wfes.tcc.edu.tw> a crit dans le message de news:
%23%23A33vNsEHA.3556@.TK2MSFTNGP10.phx.gbl...
>I have a time dimention of Year, Month and Date
> I want to know the unit sales of the first date of every month of every
> store.
> How can I implement the MDX?
>

How to get the first date

I have a time dimention of Year, Month and Date
I want to know the unit sales of the first date of every month of every
store.
How can I implement the MDX?look at this:
http://msdn.microsoft.com/library/d...br />
add2.asp
(Semi additive measures)
You are in the same case, except you want the start date instead-of the end
date in your calculations.
"ad" <ad@.wfes.tcc.edu.tw> a crit dans le message de news:
%23%23A33vNsEHA.3556@.TK2MSFTNGP10.phx.gbl...
>I have a time dimention of Year, Month and Date
> I want to know the unit sales of the first date of every month of every
> store.
> How can I implement the MDX?
>sql

How to get the date part of Datetime

Is there any other way which is more simpler(shorter) to get the date
part (I don't want the time part ) of Datetime than this ?

Convert(nvarchar, DATEPART(dd,[Date]) ) + '/' + Convert(nvarchar,
DATEPART(mm,[Date])) + '/' + Convert(nvarchar, DATEPART(yy,[Date])) As
[Date]> Is there any other way which is more simpler(shorter) to get the date
> part (I don't want the time part ) of Datetime than this ?
> Convert(nvarchar, DATEPART(dd,[Date]) ) + '/' + Convert(nvarchar,
> DATEPART(mm,[Date])) + '/' + Convert(nvarchar, DATEPART(yy,[Date])) As
> [Date]

convert(char(10),getdate(),120)
http://www.karaszi.com/SQLServer/info_datetime.asp

How to get the date of the last backup in SQL

I would like to get the date of the last database backup using SQL - any ide
as?If you don't have many tables and you want to keep the added objects added
after the backup, then you can restore the backup to a different database,
then import the data. If you have many tables and not too many objects
added, then it's better to script your added objects, restored the db from
the backup, and added the objects again.
"John Austin" <John.Austin@.ManagedNewsgroups.com> wrote in message
news:96C2AEE9-6F5A-4B38-9018-77BE1B28D34F@.microsoft.com...

quote:

---

> I would like to get the date of the last database backup using SQL - any

---

ideas?|||I think this is stored in the MSDB database, poke around and you'll find
some backup history tables.
Mike Kruchten
"John Austin" <John.Austin@.ManagedNewsgroups.com> wrote in message
news:96C2AEE9-6F5A-4B38-9018-77BE1B28D34F@.microsoft.com...

quote:

---

> I would like to get the date of the last database backup using SQL - any

---

ideas?|||I think we are at cross purposes here - I just want to be able to get the ca
lendar date that the database was last backed up. You can see this value in
Enterprise Manager by right clicking a database and choosing properties "Las
t database backup: 12/12/20
03 12:01". How can I get this information vis Transact SQL? It must be in so
me table somewhere.|||RESTORE HEADERONLY, see Books Online for detail.
"John Austin" <John.Austin@.ManagedNewsgroups.com> wrote in message
news:78845ADD-4C1B-411C-BE2E-BDFC145FD4F1@.microsoft.com...

quote:

---

> I think we are at cross purposes here - I just want to be able to get the

---

calendar date that the database was last backed up. You can see this value
in Enterprise Manager by right clicking a database and choosing properties
"Last database backup: 12/12/2003 12:01". How can I get this information vis
Transact SQL? It must be in some table somewhere.|||Or if you are not looking at the file itself but rather in the database:
use msdb
select max(sjh.run_date) from sysjobhistory sjh
inner join sysjobs sj on sj.job_id = sjh.job_id
inner join sysdbmaintplan_jobs smj on smj.job_id = sj.job_id
inner join sysdbmaintplans sm on sm.plan_id = smj.plan_id
inner join sysdbmaintplan_databases smb on smb.plan_id = sm.plan_id
inner join sysdbmaintplan_history smh on smh.plan_id = sm.plan_id
where sjh.run_status = 1
and smb.database_name = 'yourDBname'
and smh.activity = 'backup database'
"John Austin" <John.Austin@.ManagedNewsgroups.com> wrote in message
news:78845ADD-4C1B-411C-BE2E-BDFC145FD4F1@.microsoft.com...

quote:

---

> I think we are at cross purposes here - I just want to be able to get the

---

calendar date that the database was last backed up. You can see this value
in Enterprise Manager by right clicking a database and choosing properties
"Last database backup: 12/12/2003 12:01". How can I get this information vis
Transact SQL? It must be in some table somewhere.|||Thanks, Mike
Your suggestion gets me what I asked for, the date that the database was las
t backed up. Quentin's response gets me what I needed next, the creation dat
e of an actual backup file to make sure that it is the right one. Both very
useful, Thanks.|||Hi,
Please use the below query from QA,
select top 1 database_name,backup_start_date ,backup_finish_date
from msdb..backupset
where database_name='pubs'
order by backup_finish_date desc
Thanks
Hari
MCDBA
"Mike Kruchten" <mkruchten@.fsisolutions.com> wrote in message
news:uJsfwU33DHA.2404@.TK2MSFTNGP12.phx.gbl...

quote:

---

> I think this is stored in the MSDB database, poke around and you'll find
> some backup history tables.
> Mike Kruchten
>
> "John Austin" <John.Austin@.ManagedNewsgroups.com> wrote in message
> news:96C2AEE9-6F5A-4B38-9018-77BE1B28D34F@.microsoft.com...
> ideas?
>

---

|||Thanks, Hari

How to get the date of the last backup in SQL

I would like to get the date of the last database backup using SQL - any ideas?If you don't have many tables and you want to keep the added objects added
after the backup, then you can restore the backup to a different database,
then import the data. If you have many tables and not too many objects
added, then it's better to script your added objects, restored the db from
the backup, and added the objects again.
"John Austin" <John.Austin@.ManagedNewsgroups.com> wrote in message
news:96C2AEE9-6F5A-4B38-9018-77BE1B28D34F@.microsoft.com...
> I would like to get the date of the last database backup using SQL - any
ideas?|||I think this is stored in the MSDB database, poke around and you'll find
some backup history tables.
Mike Kruchten
"John Austin" <John.Austin@.ManagedNewsgroups.com> wrote in message
news:96C2AEE9-6F5A-4B38-9018-77BE1B28D34F@.microsoft.com...
> I would like to get the date of the last database backup using SQL - any
ideas?|||I think we are at cross purposes here - I just want to be able to get the calendar date that the database was last backed up. You can see this value in Enterprise Manager by right clicking a database and choosing properties "Last database backup: 12/12/2003 12:01". How can I get this information vis Transact SQL? It must be in some table somewhere.|||RESTORE HEADERONLY, see Books Online for detail.
"John Austin" <John.Austin@.ManagedNewsgroups.com> wrote in message
news:78845ADD-4C1B-411C-BE2E-BDFC145FD4F1@.microsoft.com...
> I think we are at cross purposes here - I just want to be able to get the
calendar date that the database was last backed up. You can see this value
in Enterprise Manager by right clicking a database and choosing properties
"Last database backup: 12/12/2003 12:01". How can I get this information vis
Transact SQL? It must be in some table somewhere.|||Or if you are not looking at the file itself but rather in the database:
use msdb
select max(sjh.run_date) from sysjobhistory sjh
inner join sysjobs sj on sj.job_id = sjh.job_id
inner join sysdbmaintplan_jobs smj on smj.job_id = sj.job_id
inner join sysdbmaintplans sm on sm.plan_id = smj.plan_id
inner join sysdbmaintplan_databases smb on smb.plan_id = sm.plan_id
inner join sysdbmaintplan_history smh on smh.plan_id = sm.plan_id
where sjh.run_status = 1
and smb.database_name = 'yourDBname'
and smh.activity = 'backup database'
"John Austin" <John.Austin@.ManagedNewsgroups.com> wrote in message
news:78845ADD-4C1B-411C-BE2E-BDFC145FD4F1@.microsoft.com...
> I think we are at cross purposes here - I just want to be able to get the
calendar date that the database was last backed up. You can see this value
in Enterprise Manager by right clicking a database and choosing properties
"Last database backup: 12/12/2003 12:01". How can I get this information vis
Transact SQL? It must be in some table somewhere.|||Thanks, Mik
Your suggestion gets me what I asked for, the date that the database was last backed up. Quentin's response gets me what I needed next, the creation date of an actual backup file to make sure that it is the right one. Both very useful, Thanks.|||Hi,
Please use the below query from QA,
select top 1 database_name,backup_start_date ,backup_finish_date
from msdb..backupset
where database_name='pubs'
order by backup_finish_date desc
Thanks
Hari
MCDBA
"Mike Kruchten" <mkruchten@.fsisolutions.com> wrote in message
news:uJsfwU33DHA.2404@.TK2MSFTNGP12.phx.gbl...
> I think this is stored in the MSDB database, poke around and you'll find
> some backup history tables.
> Mike Kruchten
>
> "John Austin" <John.Austin@.ManagedNewsgroups.com> wrote in message
> news:96C2AEE9-6F5A-4B38-9018-77BE1B28D34F@.microsoft.com...
> > I would like to get the date of the last database backup using SQL - any
> ideas?
>|||Thanks, Hari

how to get the date 2years before

Hi
Can anyone please help me to get the date that is 2 years before .
So that i can pull put the records tht are 2 years older...

i want to use it in a query ...

Thanks in advanceUse this select dateadd(yy, -2, getdate())

or in quesry

where date < dateadd(yy, -2, getdate())

Good Luck.sql

How to get the current date in C# and how do I pass this date to SQL Server

I am writing a ASP.NET C# web application. I will need to store the date in one field in one of my tables. It appears that I need to use the datetime data type for the date in SQL Server 2005.

So I have a question

1.) How do I get today's date in C# and how should this be passed to SQL server?

you can get the current date directly in SQL using getdate()|||Doing it in C# will return you the client system date. Getting the current date in SQL will ensure all datetime recorded in your application are in sync. If you are using the date for some sort of comparison determine the sequence of event, using the date from the client might pose some timing issue. Unless what you want is really the client's local time, you should use the Server time.|||

brgdotnet:

I am writing a ASP.NET C# web application. I will need to store the date in one field in one of my tables. It appears that I need to use the datetime data type for the date in SQL Server 2005.

So I have a question

1.) How do I get today's date in C# and how should this be passed to SQL server?

to get current date in c# use DateTime.Now.ToString()......... there is also some different methods under Now... use what is appropriate for u .

if u r using a procedure it is easier.... say proc. name saveCurrentDate

String s=DateTime.Now.ToString();

String queryString="saveCurrentDate '"+s+"'"; //// or String queryString="insert int myTable(mydate) values ('"+s+"')";

hope it will hellped u

|||

Actually I am processing some records from a comma delimited .txt file. Each item seperated by a comma, will map to a field in a SQL Server database table.

The date is in the format: 20070423, that is YYMMDD

So I need to take the date value and then write it into SQL server. So this brings us to another question. Do I need to change this date format to comply with that of SQL server? If so what format does it need to be in? I need some specifics so if you could even produce a code sample for C#, that would be great.

|||You can pass in the date in format YYYYMMDD as a string. SQL Server will implicitly convert it to string. As long as you are using Universal format YYYYMMDD, it is fine. Other format like MMDDYYYY or DDMMYYYY will be depending on the language setting that you used. So stick to YYYYMMDD and you will not go wrong.|||

I am confused about one thing though? I am reading from a text file where the format is in YYMMDD. Should I store this in SQL server as a data type of datetime or smalldatetime?

If so, I definitely have a date that, I can read from the text file, but I don't have a time? What do you think? If I did use datetime, would it just append the current time to the date I entered?

|||

you should always use proper data type for the data. In this case, you should use datetime or smalldatetime to store the date.

"I can read from the text file, but I don't have a time?"
You can still use datetime data type. Just set the time to 00:00:00. For your case, as your date string is in YYYYMMDD format without time, when you insert into table, the time will be stored as 00:00:00

how to get System date in SQL

how to get the system date or time in any format through SQL?Hi there,

think it should be

select getdate()

Carsten|||To get it to a different format, use the CAST or CONVERT functions.

blindman|||Originally posted by blindman
To get it to a different format, use the CAST or CONVERT functions.

blindman

Or

DATENAME ( datepart , date ), DATEPART ( datepart , date ) ;)|||Originally posted by snail
Or

DATENAME ( datepart , date ), DATEPART ( datepart , date ) ;)

Or select GETUTCDATE() - Greenwich Mean Time.

how to get starting datetime(monday) of the week and ending datetime of the week(sunday)

hi friends,

how to get the date of the first/last day of the week in sql...

can any one help me

Cheers,

raj

Raj,

Do clarify what would be the input.

Do u want to find the the first and last datetime of a week for an Entire Year or a month.

Or do u want to get the First and last day of a week by providing any date that falls in between.

SanDoty

|||

THANX FOR UR REPLY,

I want to get the First and last day of a week by providing CURRENT or anydate that falls in between that week

|||

This provides the beginning of the current week, and the beginning of the next week. In your query, you want rows that are >= CurrentWeek AND < NextWeek.

Declare
@.CurrentWeekStart datetime,
@.NextWeekStart datetime

SELECT
@.CurrentWeekStart = dateadd( wk, datediff( wk, 6, getdate() ), 6 ),
@.NextWeekStart = dateadd( Wk, 1, (dateadd( wk, datediff( wk, 6, getdate() ), 6 )))

SELECT @.CurrentWeekStart, @.NextWeekStart

CurrentWeek NextWeek
2007-04-29 00:00:00.000 2007-05-06 00:00:00.000

SELECT *
FROM MyTable
WHERE MyDateColumn >= @.CurrentWeekStart AND MyDateColumn < @.NextWeekStart

|||thank u Arnie..