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