Results 1 to 7 of 7
  1. #1
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065

    Getting a date in SQL Server 2012


    I have a new project assigned to me. This will be automated at some point but they want a report that will run 3 times per day and email the results to specific user's
    The first time will be around 3 AM and will run for yesterday
    The second will be around noon and will run for today
    the third will be around 6 PM and will also run for today.

    The field for the Criteria is a datetime field.

    If I use a Where datefield = getdate() nothing returns because it's trying to match the time as well as the date. Is there a function similar to getdate() that only returns todays date without the time in SQL Server?

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you need to use the convert function

    Where datefield =convert(date,getdate())

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    The condition for 1st query
    WHERE YourDatefield >= (Convert(date, GetDate())-1) AND YourDatefield < Convert(date, GetDate())

    The condition for 2nd/3rd query
    WHERE YourDatefield >= Convert(date, GetDate())

  4. #4
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Well I've made some progress but still not quite there.

    If I run the following Query
    Code:
    IF Cast((getdate()-1) as time) < '10:00'
    	Select cAST((GETDATE()-1) AS DATE)
    	eLSE  Select cAST(GETDATE() AS DATE)
    I get the results I need. But what I need to do is put it into a function that will be part of a view for a report that will run 3 times per day.

    This is my function

    Code:
    CREATE FUNCTION Auth_net_date 
    (
    	-- Add the parameters for the function here
    	@d1 Date
    )
    RETURNS date
    AS
    BEGIN
    
    IF Cast((getdate()-1) as time) < '10:00'
    	@D1 = cAST((GETDATE()-1) AS DATE)
    	eLSE  @D1 = cAST(GETDATE() AS DATE)
    
    
    	-- Return the result of the function
    	RETURN @d1 
    
    END
    It doesn't seem to matter where I put the @d1 it errors. The first cast appears to work but it's the only one am I missing something?

  5. #5
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If you cast the result as date you'll only get a date with the time set to 00:00. Try using a datettime.

    The three criteria I think you need (based on your first post) are

    Code:
    The first time will be around 3 AM and will run for yesterday - All records for yesterday = YourDateField>= DATEADD(DAY, DATEDIFF(day, 0, getdate()), -1) AND YourDateField < DATEADD(DAY, DATEDIFF(day, 0, getdate()), 0)
    The second will be around noon and will run for today - All records for today so far = YourDateField >= DATEADD(DAY, DATEDIFF(day, 0, getdate()), 0)
    the third will be around 6 PM and will also run for today. Ditto above = YourDateField >= DATEADD(DAY, DATEDIFF(day, 0, getdate()), 0)
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Quote Originally Posted by Minty View Post
    If you cast the result as date you'll only get a date with the time set to 00:00. Try using a datettime.

    The three criteria I think you need (based on your first post) are

    Code:
    The first time will be around 3 AM and will run for yesterday - All records for yesterday = YourDateField>= DATEADD(DAY, DATEDIFF(day, 0, getdate()), -1) AND YourDateField < DATEADD(DAY, DATEDIFF(day, 0, getdate()), 0)
    The second will be around noon and will run for today - All records for today so far = YourDateField >= DATEADD(DAY, DATEDIFF(day, 0, getdate()), 0)
    the third will be around 6 PM and will also run for today. Ditto above = YourDateField >= DATEADD(DAY, DATEDIFF(day, 0, getdate()), 0)

    That's what I want is the date not the time. Plus I've solved it. The function now works by putting Set in front of the Assignments

    Code:
    If Cast (gettime() AS TIME) < 10
        SET @D1 = CAST((GETTIME()-1) AS DATE)
    else
        SET @D1 = cAST(GETTIME() AS DATE)

  7. #7
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Glad you have it working.
    The variations I supplied will work on your original field without needing to convert it to a date, as they set the criteria to 12:00 am on the day in question.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access 2010 and Server 2012 R2?
    By btidwell3 in forum Access
    Replies: 1
    Last Post: 06-03-2016, 04:30 PM
  2. Replies: 1
    Last Post: 01-13-2015, 04:29 PM
  3. .MDB Backend on a 64bit 2012 server
    By chrispl in forum Access
    Replies: 1
    Last Post: 08-09-2013, 09:29 AM
  4. Replies: 4
    Last Post: 07-27-2012, 02:37 PM
  5. Replies: 1
    Last Post: 07-11-2012, 07:16 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums