x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

February 2004 Blog Posts

Come on Weekend!

CREATE FUNCTION Weekend (@Date Datetime)RETURNS datetimeASBEGINDECLARE @Weekend datetime IF (SELECT 6-DATEPART(dw,@Date)) < 0  BEGIN SELECT @Weekend =    CONVERT(datetime , CONVERT(varchar(10) , (DATEADD(dd , 13-DATEPART(dw,@Date) , @date)), 120) + ' 17:00:00')  END ELSE  BEGIN--Handles Friday itselfIF (SELECT 6-DATEPART(dw,@Date)) = 0  BEGIN  --for after 1700 but before midnight    IF (SELECT DATEDIFF(ss, @date  , CONVERT(datetime, CONVERT(varchar(10)  , @Date, 120)) + ' 17:00:00')) < 0  BEGIN  SELECT @Weekend = CONVERT(datetime   , CONVERT(varchar(10)   , (DATEADD(dd, 13-DATEPART(dw,@Date)   , @date)), 120) + ' 17:00:00')  END ELSE   --for before 1700 on Friday  BEGIN  SELECT @Weekend = CONVERT(datetime  , CONVERT(varchar(10), @Date, 120) + ' 17:00:00')  END END ELSE  --Handles days before Friday BEGIN SELECT @Weekend = CONVERT(datetime  , CONVERT(varchar(10)  , (DATEADD(dd, 6-DATEPART(dw,@Date)  , @Date)), 120) + ' 17:00:00') ENDENDReturn @WeekendENDGO SELECT DATEDIFF(mi, getdate(), dbo.Weekend(GetDate()))/60.00 As Hours_till_Margarittaville   Special thanks...

posted @ Friday, February 27, 2004 11:09 AM | Feedback (4) | Filed Under [ Disconnected Ramblings ]

The Interview

I've decided to start a list of questions for canidates (or so I can remeber when I gotta go)...ones that I've run across and was confused by (yeah, yeah, there's not enough drive space available...so I'll keep it breif) 04/15/2004 Q:What do you do with an index intersection? A: Look both ways, before you cross join.   02/12/2004 1.  Why do the 2 statements below return different results? declare @l decimal(38,2) select @l = 24.35if @l - convert(int,@l) = 0    select floor (@l)  else     select @lselect case when @l - convert(int, @l) = 0 then floor (@l)                                                else @l       end    

posted @ Thursday, February 12, 2004 1:56 PM | Feedback (10) | Filed Under [ SQL Server ]

Interogate Sprocs

Seen a bunch of people interested in Searching through Sprocs lately.  Thought it would be handy (especially for some of these god awful legacy database we're so fortunate to inherit.   Just script the sprocs in to individual files and copy to the server (if they're not there already)   And execute the code below.   USE NorthwindGO CREATE TABLE Folder (dir_output varchar(8000))CREATE TABLE Folder_Parsed (Create_Time datetime, File_Size int, [File_Name] sysname)CREATE TABLE MyWork99  (myText99 varchar(7000))CREATE TABLE MySprocs99 (myText99 varchar(7000), SprocName sysname NULL, LineNum int IDENTITY(1,1))GO SET NOCOUNT ON INSERT INTO Folder EXEC master..xp_cmdshell 'Dir d:\Sprocs\*.*' INSERT INTO Folder_Parsed (Create_Time, File_Size, [File_Name] )SELECT   Convert(datetime,Substring(dir_output,1,8) + ' '  + (Substring(dir_output,11,5)  + Case When...

posted @ Thursday, February 05, 2004 2:12 PM | Feedback (13) | Filed Under [ SQL Server ]

Powered by:
Powered By Subtext Powered By ASP.NET