Count and Group By with DateTime!

Hi all,

I have a problem with my query which is suppose to select count posts group by the date, the query works but doesnt return the count as i want, i think the problem that the datetime column contains also Time in hours which ofcourse isnt same in all rows in same day, so i dont know what to do, Here's my Query:

SELECT

PostID,Date,COUNT(Date)AS'TotalPosts'
FROMPosts
GROUP BYPostID,Date

Thank you for help

I didn't have that table available but I tested the same query against Sales.SalesOrderDetail table in AdventureWorks sample database. I believe you get the idea from it

SELECT Count(*) TheCount, [Day]
FROM (
SELECT
CAST(
CAST(MONTH(ModifiedDate) AS varchar(2) ) + '/' +
CAST(DAY(ModifiedDate) AS varchar(2) ) + '/' +
CAST(YEAR(ModifiedDate) AS varchar(4) )
AS datetime
)
as [Day]
FROM Sales.SalesOrderDetail

) TMP
GROUP BY [Day]
ORDER BY [Day]

Just change the table and field names respectively in the query

|||Amazing, Thank you alot

Corrupted Log Backup

Hi..!

Is it possible to recover data from corrupted log backup ?

Suppose we have 10 log files and 8th is corrupted then how can we recover database.

I am not facing such problem right now, but it's a question raised in my mind.

I would like to have your help, suggestions & comments. :rolleyes:

Thanks...Hi..!

Is it possible to recover data from corrupted log backup ?

Suppose we have 10 log files and 8th is corrupted then how can we recover database.

I am not facing such problem right now, but it's a question raised in my mind.

I would like to have your help, suggestions & comments. :rolleyes:

Thanks...

If the 8th log file is corrupt, then you would only be able to recover to the end of the 7th log file. It's (remotely) possible that you might be able to recover to some mid-point within the 8th log file using point-in-time recovery, but I would not count on it.

Personally, I always try to configure my production environment to do nightly full backups of all the databases and hourly transaction logs. I try to keep two full backups on disk, plus enough transaction log files to recover from the oldest full backup. I am now using a backup compression utility which speeds the process and shrinks the files making storage a non-issue. The only exception is a medium-large database that I have (300+ GB) that I backup weekly (storage capacity being the main factor in limiting backup frequency).

Remember to:
1. Practice various recovery scenarios
2. Periodically check the validity/recoverability of various backup media (disk/tape).
3. Write down your recovery strategy and file it away somewhere.

Regards,

hmscott|||Thanks... :)