Get data from last month - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Get data from last month

Author

Topic

zero1de
Posting Yak Master

105 Posts

Posted - 2014-07-23 : 07:35:39

Hi,

is it possible, that the query always select data for the last month( with getdate -1 month !?).
You can select the range with Date from(PFGLTAB) and Date until(PFGLTBIS).
If I the query will execute on 2.7.2014 so he has to get all data.
From Date=01/06/2014
To date = 6/30/2014
be.

SELECT a.FIRMA,
a.PSNR,
a.FELDNR,
a.PFLFDNR,
a.INHALT AS Cardnr,
b.INHALT AS FTE,
a.PFGLTAB,
a.PFGLTBIS,
C.KSTNR,
C.PSPERSNR,
C.PSVORNA,
C.PSNACHNA
FROM Tisoware.dbo.PNPERFELD a,
Tisoware.dbo.PNPERFELD b,
Tisoware.dbo.PERSTAMM C
WHERE (a.PSNR = b.PSNR)
AND (a.FIRMA = b.FIRMA)
AND (a.INHALT <> b.INHALT)
AND (b.PSNR = a.PSNR)
AND (b.FIRMA = a.FIRMA)
AND (b.INHALT <> a.INHALT)
AND (C.FIRMA = b.FIRMA)
AND (C.PSNR = b.PSNR)
AND (C.PSNR = a.PSNR)
AND (C.FIRMA = a.FIRMA)
AND ( ( ( ( ( a.FELDNR = '021'
AND CAST (
CAST (a.PFGLTAB AS VARCHAR) AS DATETIME) <=
GETDATE ())
AND CAST (
CAST (a.PFGLTBIS AS VARCHAR (8)) AS DATETIME) >=
CAST (GETDATE () AS DATETIME))
AND b.FELDNR = '022')
AND CAST (CAST (b.PFGLTAB AS VARCHAR) AS DATETIME) <=
GETDATE ())
AND CAST (CAST (b.PFGLTBIS AS VARCHAR (8)) AS DATETIME) >=
CAST (GETDATE () AS DATETIME))

Thx
zero1de

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-07-23 : 07:56:30

PFGLTAB>=dateadd(month,datediff(month,0,getdate())-1,0) AND
PFGLTBIS<dateadd(month,datediff(month,0,getdate()),0)

Madhivanan

Failing to plan is Planning to fail

zero1de
Posting Yak Master

105 Posts

Posted - 2014-07-23 : 08:16:12

Thx for your anwser but these produce a error:

SQL Server Database Error: Arithmetic overflow error converting expression to data type datetime.

The Field PFGLTAB and PFGLTBIS are integer !!

Thx
zero1de

quote:
Originally posted by madhivanan

PFGLTAB>=dateadd(month,datediff(month,0,getdate())-1,0) AND
PFGLTBIS<dateadd(month,datediff(month,0,getdate()),0)

Madhivanan

Failing to plan is Planning to fail

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-07-23 : 11:41:11

quote:
Originally posted by zero1de

Thx for your anwser but these produce a error:

SQL Server Database Error: Arithmetic overflow error converting expression to data type datetime.

The Field PFGLTAB and PFGLTBIS are integer !!

Thx
zero1de

quote:
Originally posted by madhivanan

PFGLTAB>=dateadd(month,datediff(month,0,getdate())-1,0) AND
PFGLTBIS<dateadd(month,datediff(month,0,getdate()),0)

Madhivanan

Failing to plan is Planning to fail

Can you fix your data types to be the correct type?

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-07-23 : 12:25:49

PFGLTAB>=convert(varchar(8),dateadd(month,datediff(month,0,getdate())-1,0),112) AND
PFGLTBIS<convert(varchar(8),dateadd(month,datediff(month,0,getdate()),0),112)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-07-23 : 13:00:31

quote:
Originally posted by ScottPletcher

PFGLTAB>=convert(varchar(8),dateadd(month,datediff(month,0,getdate())-1,0),112) AND
PFGLTBIS<convert(varchar(8),dateadd(month,datediff(month,0,getdate()),0),112)

No real need to strip off the time when converting to varchar. I'd also explicitly convert to an INT to avoid implicit conversions. But, why bother with any of that when the OP can use proper data types instead?

CAST(CONVERT(VARCHAR(8), @Date, 112) AS INT)

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-07-24 : 11:13:06

quote:
Originally posted by Lamprey

quote:
Originally posted by ScottPletcher

PFGLTAB>=convert(varchar(8),dateadd(month,datediff(month,0,getdate())-1,0),112) AND
PFGLTBIS<convert(varchar(8),dateadd(month,datediff(month,0,getdate()),0),112)
No real need to strip off the time when converting to varchar. I'd also explicitly convert to an INT to avoid implicit conversions. But, why bother with any of that when the OP can use proper data types instead?
CAST(CONVERT(VARCHAR(8), @Date, 112) AS INT)

You need to strip off time if you want an accurate comparison!

Implicit conversions on columns are bad, but not on literal comparison values. In fact, I strongly prefer them (for the vast majority of data types), because if the column's data type changes later, you avoid an incorrect result and/or an implicit column version. Say, for example, they change the column to [var]char(8) rather than int (no reason if it's a fixed YYYYMMDD that it couldn't be char). Then, by explicitly converting your value to int, you've forced SQL to convert the column to int.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-07-24 : 11:35:26

quote:
Originally posted by ScottPletcher

quote:
Originally posted by Lamprey

quote:
Originally posted by ScottPletcher

PFGLTAB>=convert(varchar(8),dateadd(month,datediff(month,0,getdate())-1,0),112) AND
PFGLTBIS<convert(varchar(8),dateadd(month,datediff(month,0,getdate()),0),112)
No real need to strip off the time when converting to varchar. I'd also explicitly convert to an INT to avoid implicit conversions. But, why bother with any of that when the OP can use proper data types instead?
CAST(CONVERT(VARCHAR(8), @Date, 112) AS INT)

You need to strip off time if you want an accurate comparison!

Implicit conversions on columns are bad, but not on literal comparison values. In fact, I strongly prefer them (for the vast majority of data types), because if the column's data type changes later, you avoid an incorrect result and/or an implicit column version. Say, for example, they change the column to [var]char(8) rather than int (no reason if it's a fixed YYYYMMDD that it couldn't be char). Then, by explicitly converting your value to int, you've forced SQL to convert the column to int.

I'm not even sure what to say. That is nonsensical and, in general, pretty poor programming form. To each there own I guess.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-07-24 : 11:58:37

You seem to know general rules but not what's really going on in SQL.

Implicit conversions are generally "bad" because, on SQL columns, they force unwanted changes in the query plan. That is not the case on single values.

As a simple factual matter, how are you going to get an accurate comparison against an integer date: 20140724, when the value you're comparing it to includes a time, i.e. the time was not stripped?/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-24 : 12:36:38

[code]DECLARE @BaseDate DATETIME = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101');
DECLARE @FromDate INT = CONVERT(CHAR(8), @BaseDate, 112);
DECLARE @ToDate INT = CONVERT(CHAR(8), DATEADD(MONTH, -1, @BaseDate), 112);

PFGLTAB >= @FromDate AND
PFGLTBIS < @ToDate[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-07-24 : 12:47:49

I know full well what is happening in SQL. I still think it is poor form to allow implicit conversions for a number of reasons. I think your example that a column could change data types and that you would code to not cast a variable/value due to that circumstance rather silly. Again, you can do whatever you want. However, your argument doesn't really make sense to me. Heck, you might as well use SQLVARIANT for everything if that is the case. But, that is probably even more silly.

I think what you were really getting at, was that you needed to strip off the DAY (get the first of the month) while doing the date math (datediff(month,0,getdate()),0)) in order to meet the OPs requirement of a months worth of data. That I have no issue with. But, your statement that you need to strip off the TIME for accuracy is incorrect. Obviously, converting a date to a string with, or without, the time portion makes no difference when you are simple taking the Date portion of the string and converting it to a INT. And thus, as no bearing on accuracy. My basic point was more directed at the OP to point out that it's better to use the proper data type(s).

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-07-24 : 12:59:56

I'm not trying to belabor the point and I know the point you are trying to make about implicit conversions. I don't want this to be an informal fallacy, but I've seen implicit conversions used in some very poor ways. I know you are only talking about a single value, so point taken. I just want people that may not be as familiar with the ins and outs of SQL to be careful with implicit conversions.

As a (bad) example. I had a co-worker who had a bit of code like this:
SELECT '8.5' + 5

He couldn't figure out why SQL didn't give him the results he wanted. His main problem is that he didn't understand the dsta type precedence rules.

So, I asked what he wanted? Was he expecting '8.55' or '13.5' or 13.5 or 13 or 14 or? As it turns out he wanted SQL to magically figure out to add the values as "numbers" and then return a string datatype: '13.5'

I had to laugh and say: SQL may be "smart" but you're going to have to help it out a bit in that circumstance. :)

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-07-24 : 13:43:21

quote:
Originally posted by Lamprey

I know full well what is happening in SQL. I still think it is poor form to allow implicit conversions for a number of reasons. I think your example that a column could change data types and that you would code to not cast a variable/value due to that circumstance rather silly. Again, you can do whatever you want. However, your argument doesn't really make sense to me. Heck, you might as well use SQLVARIANT for everything if that is the case. But, that is probably even more silly.

I think what you were really getting at, was that you needed to strip off the DAY (get the first of the month) while doing the date math (datediff(month,0,getdate()),0)) in order to meet the OPs requirement of a months worth of data. That I have no issue with. But, your statement that you need to strip off the TIME for accuracy is incorrect. Obviously, converting a date to a string with, or without, the time portion makes no difference when you are simple taking the Date portion of the string and converting it to a INT. And thus, as no bearing on accuracy. My basic point was more directed at the OP to point out that it's better to use the proper data type(s).

No, mentioning SQL variant is silly, as most reductio ad absurdum "arguments" are.

In SQL Server, data types do change, and SQL does follow data type precedence rules. Therefore, it is an absolute fact that using a higher-precedence type in a comparison value will force the column to be implicitly converted while a (var)char type does not, since it has a lower precedence.

>> Obviously, converting a date to a string with, or without, the time portion makes no difference when you are simple taking the Date portion of the string and converting it to a INT. <<

Please back that up with an example. Convert a datetime to a string, with the time, and show me how you accurately compare it to an integer column that contains only the date value. I believe your statement is factually false, but would love to see an example proving otherwise.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-07-24 : 13:54:43

quote:
Originally posted by ScottPletcher

[quote>> Obviously, converting a date to a string with, or without, the time portion makes no difference when you are simple taking the Date portion of the string and converting it to a INT. <<

Please back that up with an example. Convert a datetime to a string, with the time, and show me how you accurately compare it to an integer column that contains only the date value. I believe your statement is factually false, but would love to see an example proving otherwise.

Last post on the topic, we clearly aren't speaking the same language here.

I'm assuming you are hanging on to your implicit conversion mentality, whereas I am explicitly converting. When you explicitly convert as date to an int, time is irrelevant. It's just simple SQL my friend:

DECLARE @Date DATETIME = CONVERT(DATETIME, '2014-01-05T23:59:59.000', 126)

SELECT @Date,
CONVERT(VARCHAR(8), @Date, 112),
CAST(CONVERT(VARCHAR(8), @Date, 112) AS INT)

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-07-24 : 14:04:27

quote:
Originally posted by Lamprey

quote:
Originally posted by ScottPletcher

[quote>> Obviously, converting a date to a string with, or without, the time portion makes no difference when you are simple taking the Date portion of the string and converting it to a INT. <<

Please back that up with an example. Convert a datetime to a string, with the time, and show me how you accurately compare it to an integer column that contains only the date value. I believe your statement is factually false, but would love to see an example proving otherwise.
Last post on the topic, we clearly aren't speaking the same language here.

I'm assuming you are hanging on to your implicit conversion mentality, whereas I am explicitly converting. When you explicitly convert as date to an int, time is irrelevant. It's just simple SQL my friend:
DECLARE @Date DATETIME = CONVERT(DATETIME, '2014-01-05T23:59:59.000', 126)

SELECT @Date,
CONVERT(VARCHAR(8), @Date, 112),
CAST(CONVERT(VARCHAR(8), @Date, 112) AS INT)

But you stripped the time, after saying "with or without time".

You're just ignorant of the real consequences of what you're promoting. In particular, ints should never be used with potential date comparisons, as an implicit conversion from int to date is based on relative days, not a date format. That is, an int value of 20140716 would not be translated into a date of 07/16/2014.

Specifically, since the column is currently an int, you mistakenly force the comparison value to be an int. Say the column later changes to date (or datetime). Your code will ABEND because of your insistence on that explicit conversion. If, instead, we correctly specify the comparison value as varchar, the code continues to work exactly as expected!

Are you really trying to suggest it's "silly" to think that an int containing a YYYYMMDD date might later be changed to a date or datetime column? That seems entirely plausible to me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources