x002548's Blog - February 2004 Entries

Posts: 83
Comments: 600
Trackbacks: 40

February 2004 Entries

CREATE FUNCTION Weekend (@Date Datetime)
RETURNS datetime
AS
BEGIN
DECLARE @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 itself
IF (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')
END
END
Return @Weekend
END
GO

SELECT DATEDIFF(mi, getdate(), dbo.Weekend(GetDate()))/60.00 As Hours_till_Margarittaville

Special thanks to Preston

http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=5566

posted @ Friday, February 27, 2004 11:09 AM | Feedback (4)

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.35

if @l - convert(int,@l) = 0
    select floor (@l)
else
    select @l

select case when @l - convert(int, @l) = 0 then floor (@l)
                                                else @l
       end

posted @ Thursday, February 12, 2004 1:56 PM | Feedback (10)

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 Northwind
GO

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 Substring(dir_output,16,1) = 'a' Then ' AM' Else ' PM' End)) As Create_Time
, Convert(Int,LTrim(RTrim(Replace(Substring(dir_output,17,22),',','')))) As File_Size
, Substring(dir_output,40,(Len(dir_output)-39)) As File_Name
From    Folder
Where Substring(dir_output,1,1) <> ' '
   And (Substring(dir_output,1,1) <> ' '
   And Substring(dir_output,25,5) <> '

DECLARE @File_Name sysname, @cmd varchar(8000)

DECLARE myCursor99 CURSOR
FOR
SELECT [File_Name] FROM Folder_Parsed WHERE [File_Name] LIKE '%.PRC'

OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @File_Name

WHILE @@FETCH_STATUS = 0
BEGIN

TRUNCATE TABLE myWork99

SELECT @cmd = 'BULK INSERT myWork99 FROM '
   + ''''
   + 'D:\Sprocs\'+@File_Name
   + ''''
   + ' WITH (DATAFILETYPE = '
   + ''''
   + 'widechar'
   + ''''
   + ')'

EXEC(@cmd)

INSERT INTO mySprocs99(myText99) SELECT myText99 FROM myWork99

UPDATE mySprocs99 SET SprocName = @File_Name WHERE SprocName IS NULL

FETCH NEXT FROM myCursor99 INTO @File_Name
END

CLOSE myCursor99
DEALLOCATE myCursor99
GO

SET NOCOUNT OFF

-- Do Analysis

   SELECT (LineNum-MIN_LineNum)+1 AS Line_Number, myText99
     FROM mySprocs99 l
LEFT JOIN (
  SELECT   SprocName
   , MAX(LineNum) AS MAX_LineNum
   , MIN(LineNum) AS MIN_LineNum
      FROM mySprocs99 GROUP BY SprocName
   ) AS r
      ON l.SprocName = r.SprocName
   WHERE l.SprocName = 'dbo.GetUserAuth_sp.PRC'
GO

-- Clean up When Done

DROP TABLE Folder
DROP TABLE Folder_Parsed
DROP TABLE myWork99
DROP TABLE mySprocs99
GO

posted @ Thursday, February 05, 2004 2:12 PM | Feedback (13)

Article Categories

Archives

Post Categories

Image Galleries

Misc

SQL Server