SQL-Programming Bind Date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL-Programming Bind Date

Author

Topic

zero1de
Posting Yak Master

105 Posts

Posted - 2012-07-02 : 04:20:25

Hi Guys,

how can i bind this SQL Code to my View ? I want get with this SQL-Statment the First and Last Day of prev. Month.
I currently have the date in my view hard-coded as you can see here.
Select ... AND (BUCHEINZ.BEDATE BETWEEN 20120501 AND 20120531)
But the view should get the first and last Date of prev. month thrue this sql.

declare @date datetime
select @date = getdate()
select [firstday] = dateadd(month, datediff(month, 0, @date) - 1, 0),
[lastday] = dateadd(month, datediff(month, 0, @date), -1)

View:
ALTER VIEW [dbo].[MA_SumLohn_abw]
AS
SELECT PERSTAMM.PSPERSNR AS Personalnr,
PERSTAMM.PSNR,
PERSTAMM.PSVORNA AS Vorname,
PERSTAMM.PSNACHNA AS Nachname,
COUNT (PERSTAMM.PSPERSNR) AS [Summe Lohnarten]
FROM dbo.BUCHEINZ AS BUCHEINZ
INNER JOIN
dbo.PERSTAMM AS PERSTAMM
ON BUCHEINZ.PSNR = PERSTAMM.PSNR
AND BUCHEINZ.FIRMA = PERSTAMM.FIRMA
WHERE (BUCHEINZ.FIRMA = 1)
AND (BUCHEINZ.BEDATE BETWEEN 20120501 AND 20120531)
AND (BUCHEINZ.LOANR IN
('K', 'ARZT', 'KO', 'KK', 'KStd', 'KoA', 'KUR'))
GROUP BY PERSTAMM.PSPERSNR,
PERSTAMM.PSNR,
PERSTAMM.PSVORNA,
PERSTAMM.PSNACHNA,
PERSTAMM.PSPERSNR
GO

THX

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-02 : 04:59:44

declare @firstdayPrevMonth datetime, @lastdayPrevMonth datetime
set @firstdayPrevMonth = DateAdd(Month,DateDiff(Month,0,getdate())-1,0)
set @lastdayPrevMonth = DateAdd(dd,-1,DateAdd(Month,DateDiff(Month,0,getdate()),0))

...
AND (BUCHEINZ.BEDATE BETWEEN @firstdayPrevMonth AND @lastdayPrevMonth)
...

leads to the solution


AND (BUCHEINZ.BEDATE BETWEEN DateAdd(Month,DateDiff(Month,0,getdate())-1,0)
                         AND DateAdd(dd,-1,DateAdd(Month,DateDiff(Month,0,getdate()),0)))

No, you're never too old to Yak'n'Roll if you're too young to die.

zero1de
Posting Yak Master

105 Posts

Posted - 2012-07-02 : 08:31:16

THX for your Reply but how to insert the statment ? This kind i get error !

USE [Tisoware];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
declare @firstdayPrevMonth datetime, @lastdayPrevMonth datetime;
set @firstdayPrevMonth = DateAdd(Month,DateDiff(Month,0,getdate())-1,0);
set @lastdayPrevMonth = DateAdd(dd,-1,DateAdd(Month,DateDiff(Month,0,getdate()),0));

ALTER VIEW [dbo].[MA_SumLohn_abw]
AS
SELECT PERSTAMM.PSPERSNR AS Personalnr,
PERSTAMM.PSNR,
PERSTAMM.PSVORNA AS Vorname,
PERSTAMM.PSNACHNA AS Nachname,
COUNT (PERSTAMM.PSPERSNR) AS [Summe Lohnarten]
FROM dbo.BUCHEINZ AS BUCHEINZ
INNER JOIN
dbo.PERSTAMM AS PERSTAMM
ON BUCHEINZ.PSNR = PERSTAMM.PSNR
AND BUCHEINZ.FIRMA = PERSTAMM.FIRMA
WHERE (BUCHEINZ.FIRMA = 1)
AND (BUCHEINZ.BEDATE BETWEEN @firstdayPrevMonth AND @lastdayPrevMonth)
AND (BUCHEINZ.LOANR IN
('K', 'ARZT', 'KO', 'KK', 'KStd', 'KoA', 'KUR'))
GROUP BY PERSTAMM.PSPERSNR,
PERSTAMM.PSNR,
PERSTAMM.PSVORNA,
PERSTAMM.PSNACHNA,
PERSTAMM.PSPERSNR

quote:
Originally posted by webfred

declare @firstdayPrevMonth datetime, @lastdayPrevMonth datetime
set @firstdayPrevMonth = DateAdd(Month,DateDiff(Month,0,getdate())-1,0)
set @lastdayPrevMonth = DateAdd(dd,-1,DateAdd(Month,DateDiff(Month,0,getdate()),0))

...
AND (BUCHEINZ.BEDATE BETWEEN @firstdayPrevMonth AND @lastdayPrevMonth)
...

leads to the solution

AND (BUCHEINZ.BEDATE BETWEEN DateAdd(Month,DateDiff(Month,0,getdate())-1,0)
AND DateAdd(dd,-1,DateAdd(Month,DateDiff(Month,0,getdate()),0)))

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-02 : 10:07:25

quote:
Originally posted by zero1de

THX for your Reply but how to insert the statment ? This kind i get error !

USE [Tisoware];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
ALTER VIEW [dbo].[MA_SumLohn_abw]
AS
SELECT PERSTAMM.PSPERSNR AS Personalnr,
PERSTAMM.PSNR,
PERSTAMM.PSVORNA AS Vorname,
PERSTAMM.PSNACHNA AS Nachname,
COUNT (PERSTAMM.PSPERSNR) AS [Summe Lohnarten]
FROM dbo.BUCHEINZ AS BUCHEINZ
INNER JOIN
dbo.PERSTAMM AS PERSTAMM
ON BUCHEINZ.PSNR = PERSTAMM.PSNR
AND BUCHEINZ.FIRMA = PERSTAMM.FIRMA
WHERE (BUCHEINZ.FIRMA = 1)
~~AND (BUCHEINZ.BEDATE BETWEEN @firstdayPrevMonth AND @lastdayPrevMonth)~~

AND (BUCHEINZ.BEDATE
BETWEEN DateAdd(Month,DateDiff(Month,0,getdate())-1,0)
AND DateAdd(dd,-1,DateAdd(Month,DateDiff(Month,0,getdate()),0)))

AND (BUCHEINZ.LOANR IN
('K', 'ARZT', 'KO', 'KK', 'KStd', 'KoA', 'KUR'))
GROUP BY PERSTAMM.PSPERSNR,
PERSTAMM.PSNR,
PERSTAMM.PSVORNA,
PERSTAMM.PSNACHNA,
PERSTAMM.PSPERSNR

quote:
Originally posted by webfred

declare @firstdayPrevMonth datetime, @lastdayPrevMonth datetime
set @firstdayPrevMonth = DateAdd(Month,DateDiff(Month,0,getdate())-1,0)
set @lastdayPrevMonth = DateAdd(dd,-1,DateAdd(Month,DateDiff(Month,0,getdate()),0))

...
AND (BUCHEINZ.BEDATE BETWEEN @firstdayPrevMonth AND @lastdayPrevMonth)
...

leads to the solution

AND (BUCHEINZ.BEDATE BETWEEN DateAdd(Month,DateDiff(Month,0,getdate())-1,0)
AND DateAdd(dd,-1,DateAdd(Month,DateDiff(Month,0,getdate()),0)))

No, you're never too old to Yak'n'Roll if you're too young to die.

No, you're never too old to Yak'n'Roll if you're too young to die.

zero1de
Posting Yak Master

105 Posts

Posted - 2012-07-02 : 10:38:20

o.k know i get
Arithmetic overflow error converting expression to data type datetime.

[BEDATE] is integer NOT NULL,

quote:
Originally posted by webfred

quote:
Originally posted by zero1de

THX for your Reply but how to insert the statment ? This kind i get error !

USE [Tisoware];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
ALTER VIEW [dbo].[MA_SumLohn_abw]
AS
SELECT PERSTAMM.PSPERSNR AS Personalnr,
PERSTAMM.PSNR,
PERSTAMM.PSVORNA AS Vorname,
PERSTAMM.PSNACHNA AS Nachname,
COUNT (PERSTAMM.PSPERSNR) AS [Summe Lohnarten]
FROM dbo.BUCHEINZ AS BUCHEINZ
INNER JOIN
dbo.PERSTAMM AS PERSTAMM
ON BUCHEINZ.PSNR = PERSTAMM.PSNR
AND BUCHEINZ.FIRMA = PERSTAMM.FIRMA
WHERE (BUCHEINZ.FIRMA = 1)
~~AND (BUCHEINZ.BEDATE BETWEEN @firstdayPrevMonth AND @lastdayPrevMonth)~~

AND (BUCHEINZ.BEDATE
BETWEEN DateAdd(Month,DateDiff(Month,0,getdate())-1,0)
AND DateAdd(dd,-1,DateAdd(Month,DateDiff(Month,0,getdate()),0)))

AND (BUCHEINZ.LOANR IN
('K', 'ARZT', 'KO', 'KK', 'KStd', 'KoA', 'KUR'))
GROUP BY PERSTAMM.PSPERSNR,
PERSTAMM.PSNR,
PERSTAMM.PSVORNA,
PERSTAMM.PSNACHNA,
PERSTAMM.PSPERSNR

quote:
Originally posted by webfred

declare @firstdayPrevMonth datetime, @lastdayPrevMonth datetime
set @firstdayPrevMonth = DateAdd(Month,DateDiff(Month,0,getdate())-1,0)
set @lastdayPrevMonth = DateAdd(dd,-1,DateAdd(Month,DateDiff(Month,0,getdate()),0))

...
AND (BUCHEINZ.BEDATE BETWEEN @firstdayPrevMonth AND @lastdayPrevMonth)
...

leads to the solution

AND (BUCHEINZ.BEDATE BETWEEN DateAdd(Month,DateDiff(Month,0,getdate())-1,0)
AND DateAdd(dd,-1,DateAdd(Month,DateDiff(Month,0,getdate()),0)))

No, you're never too old to Yak'n'Roll if you're too young to die.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-02 : 10:55:20

Now we know that BEDATE is integer and not datetime.
But we don't know the values for BEDATE so how can we know the solution to your problem?

No, you're never too old to Yak'n'Roll if you're too young to die.

zero1de
Posting Yak Master

105 Posts

Posted - 2012-07-02 : 11:05:59

o.k o.k you are right :) the value of BEDATE is (YearMonthDay) 20120501

Big THX

quote:
Originally posted by webfred

Now we know that BEDATE is integer and not datetime.
But we don't know the values for BEDATE so how can we know the solution to your problem?

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-02 : 11:12:42

quote:
Originally posted by zero1de

o.k o.k you are right :) the value of BEDATE is (YearMonthDay) 20120501

Big THX

quote:
Originally posted by webfred

Now we know that BEDATE is integer and not datetime.
But we don't know the values for BEDATE so how can we know the solution to your problem?

No, you're never too old to Yak'n'Roll if you're too young to die.

AND (convert(datetime,convert(varchar(8),BUCHEINZ.BEDATE))
BETWEEN DateAdd(Month,DateDiff(Month,0,getdate())-1,0)
AND DateAdd(dd,-1,DateAdd(Month,DateDiff(Month,0,getdate()),0)))

No, you're never too old to Yak'n'Roll if you're too young to die.

zero1de
Posting Yak Master

105 Posts

Posted - 2012-07-02 : 15:16:49

THX it's works :)

quote:
Originally posted by webfred

quote:
Originally posted by zero1de

o.k o.k you are right :) the value of BEDATE is (YearMonthDay) 20120501

Big THX

quote:
Originally posted by webfred

Now we know that BEDATE is integer and not datetime.
But we don't know the values for BEDATE so how can we know the solution to your problem?

No, you're never too old to Yak'n'Roll if you're too young to die.

AND (convert(datetime,convert(varchar(8),BUCHEINZ.BEDATE))
BETWEEN DateAdd(Month,DateDiff(Month,0,getdate())-1,0)
AND DateAdd(dd,-1,DateAdd(Month,DateDiff(Month,0,getdate()),0)))

No, you're never too old to Yak'n'Roll if you're too young to die.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-07-02 : 15:44:51

quote:
Originally posted by webfred

quote:
Originally posted by zero1de

o.k o.k you are right :) the value of BEDATE is (YearMonthDay) 20120501

Big THX

quote:
Originally posted by webfred

Now we know that BEDATE is integer and not datetime.
But we don't know the values for BEDATE so how can we know the solution to your problem?

No, you're never too old to Yak'n'Roll if you're too young to die.

AND (convert(datetime,convert(varchar(8),BUCHEINZ.BEDATE))
BETWEEN DateAdd(Month,DateDiff(Month,0,getdate())-1,0)
AND DateAdd(dd,-1,DateAdd(Month,DateDiff(Month,0,getdate()),0)))

No, you're never too old to Yak'n'Roll if you're too young to die.

Although this will work - it is not an ideal solution because it will prevent usage of an index on BEDATE. It would be better to convert the parameters instead of the column:

BETWEEN cast(replace(convert(char(10), dateadd(month, datediff(month, 0, getdate()) - 1, 0), 121), '-', '') As int)
AND cast(replace(convert(char(10), dateadd(month, datediff(month, -1, getdate()) - 1, -1), 121), '-', '') As int)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-02 : 19:22:27

the bottomline is always try to use proper datatype for your columns to avoid unwanted cast operations

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources