Newbie question on physical file last mod date vs. virtual DB/Log

I think I understand the basic features of log files and how to
backup/truncate them to avoid oversized files. Where I need
help is understanding why the O/S rarely puts a new datetime
on the physical files. It appears to only update the log file
time when the log file grows. The DB file may go a month or more
without updating the timestamp. Our backups of the DB grow in
size and appear to be OK. CHECKPOINT commands don't force
the physical disk to update. Is there any other way to do it
(short of drastic measures like detaching the DB)?
Here's my concern: Suppose the server crashes due to a power
failure/UPS failure/whatever. When it restarts it will look at the
log to recover the DB. The physical DB file appears to be a month old
and the log datetime appears to be a day or two old.
If I truncated my log at any time in the last month then it seems like
it will not be able to recover correctly.
Is this really a problem or is it OK?
Environment: SQL Server 2000 on Win2K ServerWhy and when Windows updates the datetime for the file, I don't know. Perhaps somebody in the
windows forum can answer that. However:
> Here's my concern: Suppose the server crashes due to a power
> failure/UPS failure/whatever. When it restarts it will look at the
> log to recover the DB.
Correct. SQL Server know where to find the ldf file, it is stored both in the mdf file as well as in
the master database.
> The physical DB file appears to be a month old
Doesn't matter to SQL Server.
> and the log datetime appears to be a day or two old.
Can you explain what you mean by "log datetime"?
> If I truncated my log at any time in the last month then it seems like
> it will not be able to recover correctly.
What do you mean by "truncated"? Something like BACKUP LOG ... WITH TRUNCATE_ONLY? As long as you
haven't deleted the log file and replaced with an older version, you are fine. SQL server will not
remove log records needed to do recovery of the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Don Anthony" <DonAnthony@.discussions.microsoft.com> wrote in message
news:0817C2B3-1F18-4B50-A4C8-6A54D52D0D87@.microsoft.com...
>I think I understand the basic features of log files and how to
> backup/truncate them to avoid oversized files. Where I need
> help is understanding why the O/S rarely puts a new datetime
> on the physical files. It appears to only update the log file
> time when the log file grows. The DB file may go a month or more
> without updating the timestamp. Our backups of the DB grow in
> size and appear to be OK. CHECKPOINT commands don't force
> the physical disk to update. Is there any other way to do it
> (short of drastic measures like detaching the DB)?
> Here's my concern: Suppose the server crashes due to a power
> failure/UPS failure/whatever. When it restarts it will look at the
> log to recover the DB. The physical DB file appears to be a month old
> and the log datetime appears to be a day or two old.
> If I truncated my log at any time in the last month then it seems like
> it will not be able to recover correctly.
> Is this really a problem or is it OK?
> Environment: SQL Server 2000 on Win2K Server
>|||Re: Can you explain what you mean by "log datetime"?
Answer: The last modification time on the log file (i.e, both
the DB file and the Log file appear to be "old" on the disk).
Re: What do you mean by "truncated"?
Something like BACKUP LOG ... WITH TRUNCATE_ONLY?
Answer: Yes.
If the log file had a recent last modification time (the Windows File)
then it all makes sense. It seems like "magic" because it "looks" like
SQL Server does recovery with an very old DB file and a not-so-recent
Log file. Is it possible the disk files are actually updated by SQL
Server without changing the disk file last modification time?
Thank you for your help.|||> Is it possible the disk files are actually updated by SQL
> Server without changing the disk file last modification time?
Yes, this is what is happening. Again, check with the Windows people under what conditions the NTFS
file timestamps are changed. I understand that you find this ... interesting, but just don't worry
about the file timestamps.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Don Anthony" <DonAnthony@.discussions.microsoft.com> wrote in message
news:898EB1A4-A02E-456F-B82A-4391608EAD6A@.microsoft.com...
> Re: Can you explain what you mean by "log datetime"?
> Answer: The last modification time on the log file (i.e, both
> the DB file and the Log file appear to be "old" on the disk).
> Re: What do you mean by "truncated"?
> Something like BACKUP LOG ... WITH TRUNCATE_ONLY?
> Answer: Yes.
> If the log file had a recent last modification time (the Windows File)
> then it all makes sense. It seems like "magic" because it "looks" like
> SQL Server does recovery with an very old DB file and a not-so-recent
> Log file. Is it possible the disk files are actually updated by SQL
> Server without changing the disk file last modification time?
> Thank you for your help.
>|||Don Anthony wrote:
> Re: Can you explain what you mean by "log datetime"?
> Answer: The last modification time on the log file (i.e, both
> the DB file and the Log file appear to be "old" on the disk).
> Re: What do you mean by "truncated"?
> Something like BACKUP LOG ... WITH TRUNCATE_ONLY?
> Answer: Yes.
> If the log file had a recent last modification time (the Windows File)
> then it all makes sense. It seems like "magic" because it "looks" like
> SQL Server does recovery with an very old DB file and a not-so-recent
> Log file. Is it possible the disk files are actually updated by SQL
> Server without changing the disk file last modification time?
> Thank you for your help.
>
I'm not sure, but I'd think that the file timestamp for the logfile and
database file are only updated when the files are actually changed. That
could e.g. be when the file grows or shrinks.
I've just looked at one of our databases, and here the date for the
database file is 26. febr. 2006 and for the logfile it's 3. jan. 2006.
Like Tibors says, SQL server isn't using these file timestamps for
anything, so help yourself and don't worry about them...:-).
Regards
Steen

NEWBIE question for ya!

I ve been ask to diplay some infomation based a certain date and time.
I'll be using asp and mssql 2k.
Like friday between 8:00pm to 10pm to display
then on Saturday between 2:00pm and 4:00pm to display and for the rest of
the time not display. any suggetions.
I've been looking at getday()
any suggetions?
Thank you for your time in advanced.
Message posted via http://www.sqlmonster.com
Sven,
Check this article:
http://www.karaszi.com/sqlserver/inf...asp#Searching.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Sven Garber via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:a2eae88fcfcc4a3494bcb278914a66d9@.SQLMonster.c om...
> I ve been ask to diplay some infomation based a certain date and time.
> I'll be using asp and mssql 2k.
> Like friday between 8:00pm to 10pm to display
> then on Saturday between 2:00pm and 4:00pm to display and for the rest of
> the time not display. any suggetions.
> I've been looking at getday()
> any suggetions?
> Thank you for your time in advanced.
> --
> Message posted via http://www.sqlmonster.com
|||Dejan,
Thank you for the link referal...
Sharing is caring...
Thank you
Message posted via http://www.sqlmonster.com

NEWBIE question for ya!

I ve been ask to diplay some infomation based a certain date and time.
I'll be using asp and mssql 2k.
Like friday between 8:00pm to 10pm to display
then on Saturday between 2:00pm and 4:00pm to display and for the rest of
the time not display. any suggetions.
I've been looking at getday()
any suggetions?
Thank you for your time in advanced.
Message posted via http://www.droptable.comSven,
Check this article:
http://www.karaszi.com/sqlserver/in...asp#Searching.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Sven Garber via droptable.com" <forum@.droptable.com> wrote in message
news:a2eae88fcfcc4a3494bcb278914a66d9@.SQ
droptable.com...
> I ve been ask to diplay some infomation based a certain date and time.
> I'll be using asp and mssql 2k.
> Like friday between 8:00pm to 10pm to display
> then on Saturday between 2:00pm and 4:00pm to display and for the rest of
> the time not display. any suggetions.
> I've been looking at getday()
> any suggetions?
> Thank you for your time in advanced.
> --
> Message posted via http://www.droptable.com|||Dejan,
Thank you for the link referal...
Sharing is caring...
Thank you
Message posted via http://www.droptable.com

NEWBIE question for ya!

I ve been ask to diplay some infomation based a certain date and time.
I'll be using asp and mssql 2k.
Like friday between 8:00pm to 10pm to display
then on Saturday between 2:00pm and 4:00pm to display and for the rest of
the time not display. any suggetions.
I've been looking at getday()
any suggetions?
Thank you for your time in advanced.
--
Message posted via http://www.sqlmonster.comSven,
Check this article:
http://www.karaszi.com/sqlserver/info_datetime.asp#Searching.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Sven Garber via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:a2eae88fcfcc4a3494bcb278914a66d9@.SQLMonster.com...
> I ve been ask to diplay some infomation based a certain date and time.
> I'll be using asp and mssql 2k.
> Like friday between 8:00pm to 10pm to display
> then on Saturday between 2:00pm and 4:00pm to display and for the rest of
> the time not display. any suggetions.
> I've been looking at getday()
> any suggetions?
> Thank you for your time in advanced.
> --
> Message posted via http://www.sqlmonster.com|||Dejan,
Thank you for the link referal...
Sharing is caring...
Thank you
--
Message posted via http://www.sqlmonster.com

newbie question :Truncate Table side effect

We have 23 tables in the database, for each table we have a auditlog table
having similar columns and few others like date created and date updated and
AuditLogId.
When the application runs for the first time we are allocating 300MB for MDF
file and 99MB for LDF file.
Audit Logs are eating up all the space, so when we get Primary File Group
Full error, we used BCP command to transfer the content to text files and
then run the truncate table command on each AuditTable.
We are noticing a unusual behaviour after running the BCP followed by
truncate table command. The database free space suddenly is being used up at
less slower pace. We are not having any data loss or audit table data loss.
For example, before running the BCP+truncate table, we can import 30
libraries in our application, but after running the BCP+truncate table we can
import way too many around 90 libraries.
I am not able to solve this mystery because I am not familiar with SQL
Server internals how it behaves. Any help will be greatly appreciated.
Hi
Check the following:
That if you databse is set to "Full Recovery" mode, backup your transaction
log on a regular basis.
You can set the data and logs to grow automatically so that you do not run
out of space.
Regards
Mike
"Help_Me_Please" wrote:

> We have 23 tables in the database, for each table we have a auditlog table
> having similar columns and few others like date created and date updated and
> AuditLogId.
> When the application runs for the first time we are allocating 300MB for MDF
> file and 99MB for LDF file.
> Audit Logs are eating up all the space, so when we get Primary File Group
> Full error, we used BCP command to transfer the content to text files and
> then run the truncate table command on each AuditTable.
> We are noticing a unusual behaviour after running the BCP followed by
> truncate table command. The database free space suddenly is being used up at
> less slower pace. We are not having any data loss or audit table data loss.
> For example, before running the BCP+truncate table, we can import 30
> libraries in our application, but after running the BCP+truncate table we can
> import way too many around 90 libraries.
> I am not able to solve this mystery because I am not familiar with SQL
> Server internals how it behaves. Any help will be greatly appreciated.
|||If you are talking about the database free space in enterprise manager then
that is data and log so it would probably be the logs that are causing the
problem.
If the database is in full recovery mode then transaction logs will fill up
until truncated or baced up. This does not happen until after the first
backup though (log is automatically truncated until then as the backups are
useless without a full backup). Maybe your truncate is causing the same
effect and you would start using more space after the next full backup.
Have a look at
http://www.nigelrivett.net/Transacti...leGrows_1.html
If it's to do with just data then it's probably fragmentation or the way you
are checking the file space.
30 - 90 sounds a lot though.
How is the audit trail taken? Do you have something that depends on the
previous data?
|||The tables I am truncating do not have any foreign key constraints hence
truncation did not have any problem.
After the BCP + truncation, I tried to run the automated test to fill up the
database, the behaviour I noticed is the LDF file is still 101MB, while the
MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
the transaction logs are not being written?
How can I check which recovery mode is it in. I want to check before we run
into the problem and after we run the truncate cmd.
Thank you so much for the explaination.
Regards.
"Nigel Rivett" wrote:

> If you are talking about the database free space in enterprise manager then
> that is data and log so it would probably be the logs that are causing the
> problem.
> If the database is in full recovery mode then transaction logs will fill up
> until truncated or baced up. This does not happen until after the first
> backup though (log is automatically truncated until then as the backups are
> useless without a full backup). Maybe your truncate is causing the same
> effect and you would start using more space after the next full backup.
> Have a look at
> http://www.nigelrivett.net/Transacti...leGrows_1.html
> If it's to do with just data then it's probably fragmentation or the way you
> are checking the file space.
> 30 - 90 sounds a lot though.
> How is the audit trail taken? Do you have something that depends on the
> previous data?
|||> After the BCP + truncation, I tried to run the automated test to fill up the
> database, the behaviour I noticed is the LDF file is still 101MB, while the
> MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
> the transaction logs are not being written?
No. It just means that the LDF file were large enough to hold the log records produced by your
modifications.

> How can I check which recovery mode is it in.
sp_helpdb
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Help_Me_Please" <HelpMePlease@.discussions.microsoft.com> wrote in message
news:90ADC3D5-3E79-4D6F-B0A7-9A754F876A09@.microsoft.com...[vbcol=seagreen]
> The tables I am truncating do not have any foreign key constraints hence
> truncation did not have any problem.
> After the BCP + truncation, I tried to run the automated test to fill up the
> database, the behaviour I noticed is the LDF file is still 101MB, while the
> MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
> the transaction logs are not being written?
> How can I check which recovery mode is it in. I want to check before we run
> into the problem and after we run the truncate cmd.
> Thank you so much for the explaination.
> Regards.
> "Nigel Rivett" wrote:

newbie question :Truncate Table side effect

We have 23 tables in the database, for each table we have a auditlog table
having similar columns and few others like date created and date updated and
AuditLogId.
When the application runs for the first time we are allocating 300MB for MDF
file and 99MB for LDF file.
Audit Logs are eating up all the space, so when we get Primary File Group
Full error, we used BCP command to transfer the content to text files and
then run the truncate table command on each AuditTable.
We are noticing a unusual behaviour after running the BCP followed by
truncate table command. The database free space suddenly is being used up at
less slower pace. We are not having any data loss or audit table data loss.
For example, before running the BCP+truncate table, we can import 30
libraries in our application, but after running the BCP+truncate table we can
import way too many around 90 libraries.
I am not able to solve this mystery because I am not familiar with SQL
Server internals how it behaves. Any help will be greatly appreciated.Hi
Check the following:
That if you databse is set to "Full Recovery" mode, backup your transaction
log on a regular basis.
You can set the data and logs to grow automatically so that you do not run
out of space.
Regards
Mike
"Help_Me_Please" wrote:
> We have 23 tables in the database, for each table we have a auditlog table
> having similar columns and few others like date created and date updated and
> AuditLogId.
> When the application runs for the first time we are allocating 300MB for MDF
> file and 99MB for LDF file.
> Audit Logs are eating up all the space, so when we get Primary File Group
> Full error, we used BCP command to transfer the content to text files and
> then run the truncate table command on each AuditTable.
> We are noticing a unusual behaviour after running the BCP followed by
> truncate table command. The database free space suddenly is being used up at
> less slower pace. We are not having any data loss or audit table data loss.
> For example, before running the BCP+truncate table, we can import 30
> libraries in our application, but after running the BCP+truncate table we can
> import way too many around 90 libraries.
> I am not able to solve this mystery because I am not familiar with SQL
> Server internals how it behaves. Any help will be greatly appreciated.|||If you are talking about the database free space in enterprise manager then
that is data and log so it would probably be the logs that are causing the
problem.
If the database is in full recovery mode then transaction logs will fill up
until truncated or baced up. This does not happen until after the first
backup though (log is automatically truncated until then as the backups are
useless without a full backup). Maybe your truncate is causing the same
effect and you would start using more space after the next full backup.
Have a look at
http://www.nigelrivett.net/TransactionLogFileGrows_1.html
If it's to do with just data then it's probably fragmentation or the way you
are checking the file space.
30 - 90 sounds a lot though.
How is the audit trail taken? Do you have something that depends on the
previous data?|||The tables I am truncating do not have any foreign key constraints hence
truncation did not have any problem.
After the BCP + truncation, I tried to run the automated test to fill up the
database, the behaviour I noticed is the LDF file is still 101MB, while the
MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
the transaction logs are not being written?
How can I check which recovery mode is it in. I want to check before we run
into the problem and after we run the truncate cmd.
Thank you so much for the explaination.
Regards.
"Nigel Rivett" wrote:
> If you are talking about the database free space in enterprise manager then
> that is data and log so it would probably be the logs that are causing the
> problem.
> If the database is in full recovery mode then transaction logs will fill up
> until truncated or baced up. This does not happen until after the first
> backup though (log is automatically truncated until then as the backups are
> useless without a full backup). Maybe your truncate is causing the same
> effect and you would start using more space after the next full backup.
> Have a look at
> http://www.nigelrivett.net/TransactionLogFileGrows_1.html
> If it's to do with just data then it's probably fragmentation or the way you
> are checking the file space.
> 30 - 90 sounds a lot though.
> How is the audit trail taken? Do you have something that depends on the
> previous data?|||> After the BCP + truncation, I tried to run the automated test to fill up the
> database, the behaviour I noticed is the LDF file is still 101MB, while the
> MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
> the transaction logs are not being written?
No. It just means that the LDF file were large enough to hold the log records produced by your
modifications.
> How can I check which recovery mode is it in.
sp_helpdb
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Help_Me_Please" <HelpMePlease@.discussions.microsoft.com> wrote in message
news:90ADC3D5-3E79-4D6F-B0A7-9A754F876A09@.microsoft.com...
> The tables I am truncating do not have any foreign key constraints hence
> truncation did not have any problem.
> After the BCP + truncation, I tried to run the automated test to fill up the
> database, the behaviour I noticed is the LDF file is still 101MB, while the
> MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
> the transaction logs are not being written?
> How can I check which recovery mode is it in. I want to check before we run
> into the problem and after we run the truncate cmd.
> Thank you so much for the explaination.
> Regards.
> "Nigel Rivett" wrote:
> > If you are talking about the database free space in enterprise manager then
> > that is data and log so it would probably be the logs that are causing the
> > problem.
> > If the database is in full recovery mode then transaction logs will fill up
> > until truncated or baced up. This does not happen until after the first
> > backup though (log is automatically truncated until then as the backups are
> > useless without a full backup). Maybe your truncate is causing the same
> > effect and you would start using more space after the next full backup.
> >
> > Have a look at
> > http://www.nigelrivett.net/TransactionLogFileGrows_1.html
> >
> > If it's to do with just data then it's probably fragmentation or the way you
> > are checking the file space.
> > 30 - 90 sounds a lot though.
> > How is the audit trail taken? Do you have something that depends on the
> > previous data?

Newbie query-problems

Hello,
I have just started with SQL and could not find the solution to this
problem:
My access-database lists all the owners with the date they bought
and sold a property.
There are 3 tables:
Owner with fields: ID, Descr_L, Ledgernr
Property with fields: ID, Descr
Transact with fields: Date, Property, Owner, Linenr, Prev.owner,
Prev.Linenr
Each record hold a date, ID of property, ID of previous owner,
linenr of prev.owners ledger, ID of owner and linenr of ownersledger.
When I group by property and sort by date, I get a list where teh
owner on a row equals the prev.owner on the next row.
I like to get a list of breaks in this list (where the owner is NOT
equal prev.owner on the next row) showing:
Property.Descr, Owner.Descr_L, Owner.Ledgernr, Linenr
Please can anyone help me.
Thanksselect yada yada
From Transact t
Inner join ( Select * from Transact t2 where t2.property =
2.property --find the next row
and t2.date = (select min(t3.date) from Transact t3 where
t3.property = t1.property and t3.date < t1.date) ) as PrevRow
--the Previous row is the row in the transact table with the same property
id but the date is the smallest date > than the current row...
THen you can add a where clause
Where t.Prevowner != prevRow.owner
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"E.R. Visser" <ervi@.wxs.nl> wrote in message
news:ct2soi$869$1@.reader10.wxs.nl...
>
> Hello,
> I have just started with SQL and could not find the solution to this
> problem:
> My access-database lists all the owners with the date they bought
> and sold a property.
> There are 3 tables:
> Owner with fields: ID, Descr_L, Ledgernr
> Property with fields: ID, Descr
> Transact with fields: Date, Property, Owner, Linenr, Prev.owner,
> Prev.Linenr
> Each record hold a date, ID of property, ID of previous owner,
> linenr of prev.owners ledger, ID of owner and linenr of ownersledger.
> When I group by property and sort by date, I get a list where teh
> owner on a row equals the prev.owner on the next row.
> I like to get a list of breaks in this list (where the owner is NOT
> equal prev.owner on the next row) showing:
> Property.Descr, Owner.Descr_L, Owner.Ledgernr, Linenr
> Please can anyone help me.
> Thanks
>
>
>
>|||Wayne,
Thank you for your quick respons.
I'll try this solution.
Erik
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> schreef in bericht
news:%23vzTd6hAFHA.2016@.TK2MSFTNGP15.phx.gbl...
> select yada yada
> From Transact t
> Inner join ( Select * from Transact t2 where t2.property =
> 2.property --find the next row
> and t2.date = (select min(t3.date) from Transact t3 where
> t3.property = t1.property and t3.date < t1.date) ) as PrevRow
> --the Previous row is the row in the transact table with the same property
> id but the date is the smallest date > than the current row...
> THen you can add a where clause
> Where t.Prevowner != prevRow.owner
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "E.R. Visser" <ervi@.wxs.nl> wrote in message
> news:ct2soi$869$1@.reader10.wxs.nl...
>

Newbie Needs Help Getting Date

Hello,
I have a field in a table that automatically saves the Date and Time.
My field looks like this: 10/10/2003 2:24:40 PM
I need to retrieve ONLY the date portion of this field. Is there a simple way to do this? Any help is greatly appreciated!
Thanks!SELECT CONVERT(varchar(26),GetDate(),1)

Look up CONVERT in BOL for more options...|||...but you'll need to cast is as a date value again if you want to use it like a date:

SELECT CAST(CONVERT(varchar(26),GetDate(),1) as DateTime)

...or my preference because it sorts correctly as varchar...
SELECT CAST(CONVERT(varchar(10),GetDate(),120) as DateTime)

Everybody has their own preference for this, but the general method of casting as a string and then back to datetime (if necessary) is standard.

blindman

P.S.: If you still need help getting a date, check out this link:
http://personals.yahoo.com/|||heh, nice P.S. -- this forum needs the occasional chuckle

by the way, style 120, which blindman mentioned, is the ISO standard format yyyy-mm-dd

is sorts nicely as a varchar because it goes from highest to lowest (well, i didn't say that right, but i hope you know what it means)

in addition, ISO format is always correctly interpreted by all databases when inserting dates

i cannot count how many times i've seen posts on various forums from people who have run into trouble trying to insert values like '04/03/2003' (march 4th or april 3rd?)|||Originally posted by r937
i cannot count how many times i've seen posts on various forums from people who have run into trouble trying to insert values like '04/03/2003' (march 4th or april 3rd?)

Funny, I'm trying to explain that to my fellow programmers right now ... They've got the most intricate functions to fix those problems and they all screw up when someone changes the regional settings on their servers :)|||Nice PS: However, my wife MAY be mad at me for looking at personals, or maybe she is tired of me and would like that!!! LOL

I may not have described my problem well enough or probably I am not understanding since I am new to all this. This is more of what I need...

I have these dates and times: Field Name is GetDate

10/10/2003 2:24:40 PM
10/10/2003 3:34:41 PM
10/20/2003 2:24:40 PM
10/20/2003 6:54:20 PM
10/21/2003 2:24:40 PM
10/21/2003 8:34:43 PM

I need to pull out the different dates and display only one date:

10/10/2003
10/20/2003
10/21/2003

Does that make sense??
Thanks again!!!|||Please tell me you didn't name your field "GetDate". :confused:

You actually are performing two operations, the first is to truncate datetime values (which we've shown you how to do) and the second is reducing the result set to a single row for each date. You can do this using either the SELECT DISTINCT syntax, or if you need to perform aggregate functions on other columns (sum, count, avg etc...) you can use the GROUP BY syntax.

Read up on the SELECT statement in Books Online. It is very powerful, has many options, and is 90%+ of the statements DBA's write.

blindman|||Thanks for the assistance...

I didn't name the field getDate: I misstyped... it is DateSubmitted...

Thinking dateSubmitted and getDate came out of fingers... hehehe

Thanks so much for everything!!!!

Newbie HELP (ON SQL)

Hi all
I am very new to SQL and trying to select some records from a M/S
SQL database to display 7 days worth of data

My date column is set as a small date UK format

15/10/2002 00:33:13

Would I use the commands something like this

Select *
From my database
Where my table between GETDATE () AND GETDATE ()-7

Can anybody help as i am real lostohhh found it

SELECT Whatever, WhateverElse FROM TableName WHERE DateField >= DATEADD(d, -7, GETDATE())

Originally posted by webstep
Hi all
I am very new to SQL and trying to select some records from a M/S
SQL database to display 7 days worth of data

My date column is set as a small date UK format

15/10/2002 00:33:13

Would I use the commands something like this

Select *
From my database
Where my table between GETDATE () AND GETDATE ()-7

Can anybody help as i am real lost|||If you put this at the top of your script:

declare @.date1 datetime, @.date2 datetime
set @.date2 = convert(varchar(11), getdate(), 111)
set @.date1 = convert(varchar(11), getdate()-7, 111)

...then you can just use @.date1 as your from date and @.date2 as your to date. You won't have to put in any dates.

Steve

newbie help

Hi

I have a database with three fields Fund, Date and Price and want to be able to send a query to it so I get the following

Fund Last_Date_Entered Price_at_last_date
Fund1 1/12/03 1.12
Fund2 31/10/03 6.65
etc...

I have tried the following simple query

SELECT Fund, Max(Date) AS Last_Date_Entered , Last(Price) AS Price_at_last_date
FROM Database
ORDER BY Date
GROUP BY Fund

with no success as the price it returns a price not necessarily at the last date even though I have sorted by Date?? I have tried using sub-queries with HAVING and WHERE clauses to no avail so any help would be most appreciated. I can easily get the maximum date (ie last entry date) but can't for the life of me get the price corresponding to this date is there a function I don't know about? CheersNot 100% sure, but I assume what you are looking for is the Last Price on the Last Date. If this is the case, will it not work to use the following:

SELECT fund, max(date) as last_date, price as price_at_last_date
FROM database
ORDER BY date, fund;

JoeB|||TRY THIS

SELECT
Fund, Date , Price
FROM Database
ORDER BY Date DESC <-- THIS WILL DISPLAY THE LATEST FIRST

REGARDS

EDWINJAMES|||What is Last()?|||apologies ignore the last() bit look at it again|||or if u just want the latest price then enter the following

SELECT
FUND, DATE AS LAST_DATE_ENTERED , PRICE AS PRICE_AT_LAST_DATE
FROM DATABASE
WHERE DATE SELECT MAX(DATE) FROM DATABASE|||Just to clarify there are a number of entries in the database under each fund at different dates... So firstly I want to group the Funds using a subquery perhaps

Fund1
.... Date, Price
.... 12/01/03, 1.15
.... 16/02/03, 1.21
Fund 2
.... Date, Price
.... 15/01/03, 3.23
.... 19/02/03, 4.01
etc.

Then select the last date entry and its corresponding price|||TRY THIS

SELECT FUND, DATE AS LAST_DATE_ENTERED , PRICE AS PRICE_AT_LAST_DATE
FROM DATABASE
WHERE (DATE SELECT MAX(DATE) FROM DATABASE
AND FUND = SELECT DISTINCT(FUND FROM DATEBASE))
ORDER BY 1,2|||Use GROUP BY with fund and max(date), date may need to be expanded, then apply an INNER JOIN to this result set with the original table to display the price aswell.|||Originally posted by r123456
Use GROUP BY with fund and max(date), date may need to be expanded, then apply an INNER JOIN to this result set with the original table to display the price aswell.

Thanks this worked a treat using two INNER JOINS one between Query1 to find the Last_Date_Entered AND Date of the Database then the other on the Fund. Here is the code

Query1
______
SELECT Fund, Max(Date) AS LAST_DATE_ENTERED
FROM DAtabase
GROUP BY Fund

Query2
______
SELECT Query1.Fund, AVG(Database.Price)
FROM Query1 INNER JOIN Database ON (Query1.Fund = Database.Fund) AND (Query1.LAST_DATE_ENTERED = Database.Date)
GROUP BY Query1.Fund

The average is necessary if there are two prices entered for any one date equally MAX could have been used. Thanks once again to all those who posted. Derek

Newbie Datetime Parameter problem

Hi, I have setup start date and end date parameters, however when my select gets the results from between the dates, if the dates are both set to today, no results are produced. I think its because I need to add a day to the end date parameter. How can I do this?

Thanks, Dan

Dan,

What you'll need to do is add one to your end date, like you said. You can do that by updating your SQL coding to something like this:

where ....
and DateField between @.StartDate and dateadd(day, 1, @.EndDate)
...

Hope this helps.

Jarret

|||

Thats perfect.

Thank you.

Newbie Date Question

Hi all,
I need a 'select' query that can give me the date of the first 'Monday' in a
month for any given month, but am not sure how to go about this.
Say I want to know the date of the first Monday in June ?
regards,
Colin H.SELECT DATEADD(wk,
DATEDIFF(wk,0,DATEADD(dd,6-DATEPART(DAY,GETDATE()),GETDATE())), 0)
"Colin H" <ssssss@.koko.com> wrote in message
news:d02i9n$suh$1@.titan.btinternet.com...
> Hi all,
> I need a 'select' query that can give me the date of the first 'Monday' in
a
> month for any given month, but am not sure how to go about this.
> Say I want to know the date of the first Monday in June ?
> regards,
> Colin H.
>
>|||http://www.aspfaq.com/2519
http://www.aspfaq.com/
(Reverse address to reply.)
"Colin H" <ssssss@.koko.com> wrote in message
news:d02i9n$suh$1@.titan.btinternet.com...
> Hi all,
> I need a 'select' query that can give me the date of the first 'Monday' in
a
> month for any given month, but am not sure how to go about this.
> Say I want to know the date of the first Monday in June ?
> regards,
> Colin H.
>
>|||Thanks for the help. That works a treat.
Colin H.|||That seems like a lot of work just to get the first monday of a given month.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O7gDA0pHFHA.2936@.TK2MSFTNGP15.phx.gbl...
> http://www.aspfaq.com/2519
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Colin H" <ssssss@.koko.com> wrote in message
> news:d02i9n$suh$1@.titan.btinternet.com...
in
> a
>|||> That seems like a lot of work just to get the first monday of a given
month.
Maybe, but the table really has a small footprint, and I guess if you have
an application that is used only to get the first monday of a given month,
you're probably right. However there are dozens of other uses as well.
A|||Very true, I actually created the table locally and populated it based on
that article, it's a neat design - I'm not knocking that. I like it
actually. Just seemed like overkill for the question. But I can see many
uses for the article you posted.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23xy3zDqHFHA.236@.TK2MSFTNGP14.phx.gbl...
> month.
> Maybe, but the table really has a small footprint, and I guess if you have
> an application that is used only to get the first monday of a given month,
> you're probably right. However there are dozens of other uses as well.
> A
>

Newbie : Hard sql query?

Hey,
I have a table that records all my bank transactions. So in it there
is one entry per transaction. This entry has the date the transaction
occured.
All I want to do is perform a query that returns how many transactions
I had for each month.
Ie.
jan : 24 transactions
feb : 19 transactions
etc
is this possible? I know I can do "counts" ... but it seems hard
because I need to split the results return by the month it occurs in.
Any help you can give would be greatly appreciated!!
Thanx
Ryan RittenNot the most efficient way, but...
SELECT YEAR(TransactionDate), MONTH(TransactionDate), COUNT(*)
FROM TransactionTable
-- if you want to limit to specific year:
-- WHERE TransactionDate >= '20050101' AND TransactionDate < '20060101'
GROUP BY YEAR(TransactionDate), MONTH(TransactionDate)
ORDER BY 1,2
"Sparticus" <sparticusREMOVE@.thesparticusarena.com> wrote in message
news:1129914248.191858.104870@.f14g2000cwb.googlegroups.com...
> Hey,
> I have a table that records all my bank transactions. So in it there
> is one entry per transaction. This entry has the date the transaction
> occured.
> All I want to do is perform a query that returns how many transactions
> I had for each month.
> Ie.
> jan : 24 transactions
> feb : 19 transactions
> etc
> is this possible? I know I can do "counts" ... but it seems hard
> because I need to split the results return by the month it occurs in.
> Any help you can give would be greatly appreciated!!
> Thanx
> Ryan Ritten
>|||> I have a table that records all my bank transactions. So in it there
> is one entry per transaction. This entry has the date the transaction
> occured.
> All I want to do is perform a query that returns how many transactions
> I had for each month.
>
select Year(Date) As Year ,month(Date) Month, count(*) as TransCount from
YourTable
group by Year(Date),month(Date)
Francesco Anti|||Sparticus,
This should give you what you want. You'll need to WHERE for a specify year
or year range. If you want month names and/or you want the months with 0 or
as columns instead of rows...let me know.
SELECT DATEPART(MM,TDATE)AS 'MONTH', COUNT(*) 'TRANSACTION COUNT'
FROM #TRANSACTIONS
GROUP BY DATEPART(MM,TDATE)
HTH
Jerry
"Sparticus" <sparticusREMOVE@.thesparticusarena.com> wrote in message
news:1129914248.191858.104870@.f14g2000cwb.googlegroups.com...
> Hey,
> I have a table that records all my bank transactions. So in it there
> is one entry per transaction. This entry has the date the transaction
> occured.
> All I want to do is perform a query that returns how many transactions
> I had for each month.
> Ie.
> jan : 24 transactions
> feb : 19 transactions
> etc
> is this possible? I know I can do "counts" ... but it seems hard
> because I need to split the results return by the month it occurs in.
> Any help you can give would be greatly appreciated!!
> Thanx
> Ryan Ritten
>|||wow...thanx everyone. Learn somethign new everyday :)|||group your data by datepart(month,datefield),datepart(year,
datefield)
http://sqlservercode.blogspot.com/
"Sparticus" wrote:

> Hey,
> I have a table that records all my bank transactions. So in it there
> is one entry per transaction. This entry has the date the transaction
> occured.
> All I want to do is perform a query that returns how many transactions
> I had for each month.
> Ie.
> jan : 24 transactions
> feb : 19 transactions
> etc
> is this possible? I know I can do "counts" ... but it seems hard
> because I need to split the results return by the month it occurs in.
> Any help you can give would be greatly appreciated!!
> Thanx
> Ryan Ritten
>|||CREATE TABLE #MyTransaction
(
id int NOT NULL IDENTITY (1, 1),
[date] datetime NULL,
amount money NULL
) ON [PRIMARY]
insert into #MyTransaction
([date], amount)
values
('1-1-05', 100)
insert into #MyTransaction
([date], amount)
values
('1-10-05', 100)
insert into #MyTransaction
([date], amount)
values
('1-20-05', 100)
insert into #MyTransaction
([date], amount)
values
('2-1-05', 100)
insert into #MyTransaction
([date], amount)
values
('2-5-05', 100)
insert into #MyTransaction
([date], amount)
values
('2-7-05', 100)
insert into #MyTransaction
([date], amount)
values
('3-8-05', 10)
select datepart(yy,[date]) as Year, datename(mm,[date]) as month, count(id)
as Count
from #MyTransaction
group by datepart(yy,[date]), datename(mm,[date])
drop table #MyTransaction
"Sparticus" <sparticusREMOVE@.thesparticusarena.com> wrote in message
news:1129914248.191858.104870@.f14g2000cwb.googlegroups.com...
> Hey,
> I have a table that records all my bank transactions. So in it there
> is one entry per transaction. This entry has the date the transaction
> occured.
> All I want to do is perform a query that returns how many transactions
> I had for each month.
> Ie.
> jan : 24 transactions
> feb : 19 transactions
> etc
> is this possible? I know I can do "counts" ... but it seems hard
> because I need to split the results return by the month it occurs in.
> Any help you can give would be greatly appreciated!!
> Thanx
> Ryan Ritten
>

newbe ? re:date format

Hi, I'm developing a page in asp.net using a sqldb and one of the values I'm sending to the database is a date/time value. I only want the table to show the date in short format, no time. Is there a way to restrict or convert the data in the table design mode?
Thanks
RaifSQL Server dates are stored as numeric values that interpreted as dates with time of day. Formatting of output should be handled by your user interface, not the database server.|||Thanks I sort suspected that as I started writing the post.
Thanks again|||Go with a smalldatetime in the sql table so that you are only using a (4). The go with a .ToString() and something like this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimeclasstostringtopic4.asp
Hope it helps.|||If you're doing straight SELECT from your ASP, you can use CONVERT(char(10), <your_date_field>, 101) on your date/time field.

NEWBE

I have web forms on my site that store data in an sql dbase.
I have a DATE column that i would like to store each date, that each request
was submitted. The only function that i found to automatically populate that
field was TIMESTAMP... any ideas on how i can autopopulate just the DATE
field?
thanks
sorry if im in the wrong room.
If you want the current date, time you can use GetDate() in
your SQL statement. Or you can use it as a default value.
Depends on what you mean by populate and if it's just for
the creation of a new record, etc.
-Sue
On Wed, 15 Mar 2006 09:17:29 -0800, tony
<tony@.discussions.microsoft.com> wrote:

>I have web forms on my site that store data in an sql dbase.
>I have a DATE column that i would like to store each date, that each request
>was submitted. The only function that i found to automatically populate that
>field was TIMESTAMP... any ideas on how i can autopopulate just the DATE
>field?
>thanks
>sorry if im in the wrong room.

NEWBE

I have web forms on my site that store data in an sql dbase.
I have a DATE column that i would like to store each date, that each request
was submitted. The only function that i found to automatically populate tha
t
field was TIMESTAMP... any ideas on how i can autopopulate just the DATE
field?
thanks
sorry if im in the wrong room.If you want the current date, time you can use GetDate() in
your SQL statement. Or you can use it as a default value.
Depends on what you mean by populate and if it's just for
the creation of a new record, etc.
-Sue
On Wed, 15 Mar 2006 09:17:29 -0800, tony
<tony@.discussions.microsoft.com> wrote:

>I have web forms on my site that store data in an sql dbase.
>I have a DATE column that i would like to store each date, that each reques
t
>was submitted. The only function that i found to automatically populate th
at
>field was TIMESTAMP... any ideas on how i can autopopulate just the DATE
>field?
>thanks
>sorry if im in the wrong room.