Date definition

[jabmod]

What is the SQL code to define variables
@Begindt to generate 12/01/2004
@Enddt to generate 01/01/2005

Thanks.

 

[dbomrrsm]

declare @Begindt datetime
set @Begindt = '12/01/2004'
select @Begindt

[blue]DBomrrsm[/blue]

 

[dbomrrsm]

set dateformat MDY
declare @Begindt datetime
set @Begindt = '12/01/2004'
select @Begindt

--if 12/01/2004 is 12th Jan 2004 otherwise set dateformat MDY

[blue]DBomrrsm[/blue]

 

[jabmod]

Thanks but I do not want to hard code the dates. The purpose is to pick up past month data at the beginning of each new month. Thanks you.

 

[SQLBill]

Here's what I run. I got this from this site sometime ago:

CREATE PROCEDURE usp_monthlyreport AS
SET NOCOUNT ON
/* set variables for the beginning and end of month */

DECLARE @start_dt SMALLDATETIME
        , @end_dt SMALLDATETIME

IF MONTH(GETDATE()) = 1
  BEGIN
    SELECT
      @start_dt = '12/1/' + CONVERT(CHAR(4), YEAR(GETDATE()) - 1)
  END
ELSE 
  BEGIN
    SELECT
      @start_dt = CONVERT(CHAR(2), MONTH(GETDATE()) - 1) + '/1/' + CONVERT(CHAR(4), YEAR(GETDATE()))
  END

SELECT
  @end_dt = DATEADD(MONTH, 1, @start_dt)

-SQLBill

Posting advice: FAQ481-4875

 

[donutman]

[Blue]SELECT[/Blue] [Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]mm[Gray],[/Gray][Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]mm[Gray],[/Gray]0[Gray],[/Gray][Fuchsia]GetDate[/Fuchsia][Gray]([/Gray][Gray])[/Gray][Gray])[/Gray][Gray]-[/Gray]1[Gray],[/Gray]0[Gray])[/Gray][Gray],[/Gray]
       [Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]mm[Gray],[/Gray][Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]mm[Gray],[/Gray]0[Gray],[/Gray][Fuchsia]GetDate[/Fuchsia][Gray]([/Gray][Gray])[/Gray][Gray])[/Gray][Gray],[/Gray]0[Gray])[/Gray]

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[dbomrrsm]

declare @Begindt datetime, @Enddt datetime
set @Begindt = (select dateadd(month,-1,
(dateadd(day, -(datepart(day,getdate()-1)),
 (dateadd(day,datediff(day,0,getdate()),0))))))
set @Enddt = (select dateadd(day,-1,
(dateadd(day, -(datepart(day,getdate()-1)),
 (dateadd(day,datediff(day,0,getdate()),0)))))+1)
select @Begindt
select @Enddt

[blue]DBomrrsm[/blue]

 

[donutman]

DBomrrsm, the Select within the Set is superfluous.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[dbomrrsm]

useful to know - thanks Karl

[blue]DBomrrsm[/blue]