Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8

    Unanswered: VB Function to SQL function help

    I have this function in in a MSaccess database

    Leaning the SQL way slowly

    Code:
    Function WEEKEND(dat) As Date
    'Author StePhan Mckillen
    'Created 01/07/1999
    'Returns the Saturdays date of a date
    
    If IsNull(dat) Then Exit Function
       dat = DateSerial(year(dat), Month(dat), Day(dat))
    If dat Mod 7 > 0 Then 
       WEEKEND = dat - dat Mod 7 + 7
    Else
       WEEKEND = dat
    End If
    End Function
    it work out the saturday weekending for me

    Code:
    -- =============================================
    -- Author: 	Stephan McKillen
    -- Created: 	01/03/2009
    -- Description:	Return the Saturday Date of a Date
    -- =============================================
    
    ALTER FUNCTION dbo.weekEnd
    ( 
        @WE DATETIME 
    )
    RETURNS DATETIME 
    AS 
    BEGIN 
    	DECLARE @FA int;
    	-- work out the mod of the date
    	--============================
    	SET @FA = (@WE % 7) ;
    	--============================
    	IF (@FA>0)
    	-- how to convert this to SQL => WEEKEND = dat - dat Mod 7 + 7
    	ELSE
    
    	END
    
        RETURN @WE
    END
    ????
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    OK I have just found out you can't MoD the Date bugger

    more reading then

    so can i

    @FA = (CONVERT(int, @WE, 101) % 7) ;

    i just thinking and working it out
    Last edited by myle; 02-28-09 at 18:44.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    --SQL Server base date (0 = 1900-01-01) is a Monday
    --  Therefore 5 (1900-01-6) is a saturday
    
    IF DateDiff(dd, 5, @wd) % 7 = 0
      BEGIN
        SELECT @wd
      END
    ELSE
      BEGIN
        SELECT DateAdd(dd, (-1 * (DateDiff(dd, 5, @wd) % 7)) + 7, @wd)
      END
    Note that this is untested.

    To make this even shorter, the following should give the same result
    Code:
    SELECT DateAdd(dd, (-1 * Coalesce(NullIf(DateDiff(dd, 5, @wd) % 7), 0, 7)) + 7, @wd)
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Thanks Gvee That make cents
    I can see how it works but how to get it working

    how do I put it into my weekend function.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    ALTER FUNCTION dbo.weekend ( 
       @we datetime
    )
    RETURNS datetime
    AS
      BEGIN
        DECLARE @result datetime
    
        IF DateDiff(dd, 5, @we) % 7 = 0
          BEGIN
            SET @result = @we
          END
        ELSE
          BEGIN
            SET @result = DateAdd(dd, (-1 * (DateDiff(dd, 5, @we) % 7)) + 7, @we)
          END
    
        RETURN @result
      END
    Or
    Code:
    ALTER FUNCTION dbo.weekend ( 
       @we datetime
    )
    RETURNS datetime
    AS
      BEGIN 
        RETURN DateAdd(dd, (-1 * Coalesce(NullIf(DateDiff(dd, 5, @we) % 7, 0), 7)) + 7, @we)
      END
    Note that I had to amend the second function because I mis-bracketed
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Thanks gvee

    starting to see the light at the end of the tunnel
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •