TSQL Blogs

T-SQL Get dates on a specific day when you don't care about time

Print
PDF

Written by John Wiltshire | 26 February 2010

Quite often I have to write queries to return rows where something happened on a specific day. But the values in the table could be anytime within that day.

Previous I would pass in a start date, end date to do this, but the query then needs 2 parameters and it looks a bit cumbersome.

I came across this method which I now employ. Don't know if this has much performance impact, but it certainly is quicker to code and takes less parameters from calling application.

Where
(
DateDiff(day, @ReportStartDate,TerminalSession.StartDate) = 0

--StartDate >= @ReportStartDate
--And
--EndDate >= @ReportEndDate
)

SQL Server Transaction Log Full

Print
PDF

Written by John Wiltshire | 24 February 2010

Sometimes, for whatever reason the transaction log gets full up.
And shrinking via management studio doesn't work;

sp_detach_db [database name]
Then rename or delete the log file

Then
EXEC sp_attach_db @dbname = N'pubs',
  @filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
This will create a new blank log file and get you up and running.

Useful bits of info on Server and Database

Print
PDF

Written by John Wiltshire | 24 February 2010

--Useful bits of info on Server and Database

--Returns Info on DB called MyDBName
exec sp_helpdb MyDBName

--Returns Info on ALL DB's in Server
exec master.dbo.sp_msforeachdb "exec [?].dbo.sp_spaceused"

--Returns Info on Version of Server Running eg. Express, Standard etc.
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')