0

I am querying data with a date I insert at the end of the query at the end of EXEC command but I want to use the GETDATE function to query yesterday if possible since I plan on running these queries on a schedule and not manually. I don't want to have to go and edit the date every day.

USE [EXP]
GO

DECLARE @DateFrom datetime;
SET @DateFrom = DATEADD(day, -1, cast(getdate() as date));

/****** Object:  Table [dbo].[Trans]    Script Date: 6/11/2021 9:25:00 AM ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Trans]') AND type in (N'U'))
DROP TABLE [dbo].[Trans]
GO

/****** Object:  Table [dbo].[Trans]    Script Date: 6/11/2021 9:25:00 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Trans](
    [Account] [varchar](8) NULL,
    [Amount] [money] NOT NULL,
    [typename] [varchar](20) NULL,
    [DateEffective] [datetime] NULL,
    [TranDesc] [varchar](200) NULL,
    [DRAmount] [money] NULL,
    [CRAmount] [money] NULL,
    [CUUser] [varchar](50) NULL,
    [Deleted] [bit] NULL
) ON [PRIMARY]
GO


--set identity_insert Trans on
insert into Trans
    ([Account]
    ,[Amount]
    ,[typename]
    ,[DateEffective]
    ,[TranDesc]
    ,[DRAmount]
    ,[CRAmount]
    ,[CUUser]
    ,[Deleted])
EXEC [FER_DP].[DP].[dbo].[Excel_Trans] @DateFrom;
Jack Baum
  • 1
  • 1
  • So what issue are you having? If you mean you can't use a function as a parameter, see https://stackoverflow.com/questions/17367510/getdate-causes-error-when-used-with-exec – SOS Feb 23 '22 at 15:26
  • Have you looked at the T-Sql datetime functions that might meet your requirements - `dateadd`? – Stu Feb 23 '22 at 15:26
  • There is no query in your question that tries to get rows from yesterday. So how can we see what is wrong with it ? – GuidoG Feb 23 '22 at 15:29
  • `dateadd(day, -1, cast(getdate() as date) )` – Serg Feb 23 '22 at 15:40
  • `EXEC [FER_DP].[DP].[dbo].[Excel_Trans] '1/1/2022'` So you simply want to pass the current date and time (or some variation) as the parameter, rather than a hard-coded one? – SMor Feb 23 '22 at 15:42
  • That is correct, preferably pass through the previous day to whenever it runs. – Jack Baum Feb 23 '22 at 15:50
  • Declare a variable, set the variable as desired, then pass it. As already commented, DATEADD can be used to calculate the "previous date". Beware of holidays, weekends, other non-working dates. – SMor Feb 23 '22 at 15:59
  • I declared the variable but I'm not certain its in the right spot. As you can see above I added it towards the top. And then called it at the end of the EXEC – Jack Baum Feb 23 '22 at 16:09
  • 1
    GO is not a tsql command. It is a batch separator. When you declare a variable in one batch, any following batches cannot "see" it. Move that declaration immediately above the line containing your insert statement. Note that when you subtract one day from the current date, you get "yesterday" with the current time. I hope that is what you intend. – SMor Feb 23 '22 at 16:53

0 Answers0