Interger Concatenation

Hey,
Does anyone know how to do integer concatenation using sql?
Say for instance I have 2004 and 12, I need 200412 and not 2016.
Can this be done?
ThanksDECLARE @.x int, @.y int
SELECT @.x = 2004, @.y = 12
SELECT CONVERT(int,(CONVERT(varchar(15),@.x) + CONVERT(varchar(15),@.y)))|||Thanks for the reply,

But what about if what I am doing is using the datepart function.

ex.

datepart("yy", '12/12/2004') + datepart("mm", '12/12/2004')

I need 200412.

Still work the same way?

Thanks|||I could have said yes and yes and have answered the questions you aske dwith that. However, turn them into character strings and then concatenate the results.

i.e.
declare @.year int, @.month int, @.result varchar(50)
select @.year = 2004, @.month = 12
select @.result = convert(varchar(20),@.year) + convert(varchar(20),@.month
select @.result|||Ya know...it's alot easier to get you the right answer when you ask the right question.

Please don't tell me you're going to store this data in a table though

SELECT CONVERT(varchar(4),DATEPART(yy, '12/12/2004')) + CONVERT(varchar(2),DATEPART(mm, '12/12/2004'))|||Ya, I guess the right question makes for the right answer.

And nope, its not going in a table, just needs it to make some comparisons.

Thanks for the help.|||Ya, I guess the right question makes for the right answer.

And nope, its not going in a table, just needs it to make some comparisons.

Thanks for the help.

Compare it to what? To data that looks like that in some other table?

That'll scan for sure...|||Actually, I am in the process of upgrading a reporting system the uses two date inputs to bring back the correct timeframe of data, say from 11/01/2004 to 11/30/2004, but what happens now is that if you went from one year to the next say from 11/01/2004 to 02/30/2005 is crashes. So I was given the task to fix this.

As it works now, it only uses the month part of the date to make comparsions. It only scans for data between the months of the initial date input.

Eventually I will have to comapre full years, but for now I just needs to get it working from year to year.

Any suggestions?|||...forest for the trees, gentlemen:

convert(char(6), cast('12/12/04' as datetime), 112)

...or if it already a datetime value, then just:

convert(char(6), [YourValue], 112)

...but I suspect from your last post that you should not be converting this at all, and that you are better off using SQL Server's rich set of date functions than converting this to a six character string. Give us an example of the BETWEEN statement that you are using...|||This is the table (should be a temp table) where the months between the dates are stored for comparsion

create table monthspan(currentmonth int)

declare @.monthCount int
set @.monthCount = datepart(mm, @.FromDate)
while (@.monthCount <= datepart(mm, @.ToDate))
BEGIN
insert monthspan select @.monthCount
set @.monthCount = @.monthCount + 1
END|||I'm assuming the actual data is in date form...

You should be using DATEDIFF|||How would using DATEDIFF work if I needed to compare values to it?|||USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 datetime)
GO

INSERT INTO myTable99(Col2)
SELECT '1/1/2003' UNION ALL SELECT '2/1/2003' UNION ALL SELECT '3/1/2003' UNION ALL
SELECT '4/1/2003' UNION ALL SELECT '5/1/2003' UNION ALL SELECT '6/1/2003' UNION ALL
SELECT '7/1/2003' UNION ALL SELECT '8/1/2003' UNION ALL SELECT '9/1/2003' UNION ALL
SELECT '10/1/2003' UNION ALL SELECT '11/1/2003' UNION ALL SELECT '12/1/2003' UNION ALL
SELECT '1/1/2004' UNION ALL SELECT '2/1/2004' UNION ALL SELECT '3/1/2004' UNION ALL
SELECT '4/1/2004' UNION ALL SELECT '5/1/2004' UNION ALL SELECT '6/1/2004' UNION ALL
SELECT '7/1/2004' UNION ALL SELECT '8/1/2004' UNION ALL SELECT '9/1/2004' UNION ALL
SELECT '10/1/2004' UNION ALL SELECT '11/1/2004' UNION ALL SELECT '12/1/2004'
GO

-- Let's get the last 14 months of Data

SELECT * FROM myTable99
WHERE DATEDIFF(m,Col2,GetDate()) < 14
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GOsql

Intensively used function in view needs a minimum and maximum from a table

I have a problem (who not?) with a function which i'm using in a view.
This function is a function which calculates a integer value of a
date. For example: '12/31/2004 00:00:00" becomes 20041231. This is
very handy in a datawarehouse and performes superfast. But here is my
problem.

My calendar table is limited by a couple of years. What happens is
that sometimes a value is loaded which is not in the range of the
Calendardate. What we want to do is when a date is loaded is that this
function insert a minimum date when date < minimum date and a maximum
date when date > maximum date.

Yes i know you're thinking : This is datamanipulation and yes this is
true. But now we loose information in our cubes and reports by inner
joining. So if we can use a minimum and a maximum than a user would
say: "This is strange, a lot of values on 1980/1/1!" instead of "I
think that i have not all the data!"

Greetz

HennieHi

If you LEFT or RIGHT JOIN to the calendar table you will get a NULL value
for the column, you can then is CASE to determine the value

CREATE FUNCTION ConvertDate (@.datevalue datetime)
RETURNS INT
AS
BEGIN
DECLARE @.dateint INT
SELECT @.dateint = CAST( CASE WHEN A.Date < '20030101' THEN '19800101'
WHEN A.Date > '20051231' THEN '99991231'
ELSE CONVERT(CHAR(4),C.[Year]) + RIGHT('0'+
CONVERT(VARCHAR(2),C.[Month]),2) + RIGHT('0'+ CONVERT(VARCHAR(2),C.[Day]),2)
END AS INT )
FROM ( SELECT @.datevalue AS [Date] ) A
LEFT JOIN CALENDAR C ON C.[Date] = A.[Date]
RETURN @.dateint
END
GO

John

"Hennie de Nooijer" <hdenooijer@.hotmail.com> wrote in message
news:191115aa.0412300238.7dee0f85@.posting.google.c om...
>I have a problem (who not?) with a function which i'm using in a view.
> This function is a function which calculates a integer value of a
> date. For example: '12/31/2004 00:00:00" becomes 20041231. This is
> very handy in a datawarehouse and performes superfast. But here is my
> problem.
> My calendar table is limited by a couple of years. What happens is
> that sometimes a value is loaded which is not in the range of the
> Calendardate. What we want to do is when a date is loaded is that this
> function insert a minimum date when date < minimum date and a maximum
> date when date > maximum date.
> Yes i know you're thinking : This is datamanipulation and yes this is
> true. But now we loose information in our cubes and reports by inner
> joining. So if we can use a minimum and a maximum than a user would
> say: "This is strange, a lot of values on 1980/1/1!" instead of "I
> think that i have not all the data!"
> Greetz
> Hennie|||On 30 Dec 2004 02:38:51 -0800, Hennie de Nooijer wrote:

>I have a problem (who not?) with a function which i'm using in a view.
>This function is a function which calculates a integer value of a
>date. For example: '12/31/2004 00:00:00" becomes 20041231. This is
>very handy in a datawarehouse and performes superfast. But here is my
>problem.
(snip)

Hi Hennie,

Is this conversion all that your function does? If so, you might want to
try the following alternative (using CURRENT_TIMESTAMP as example; replace
it with your date column / parameter):

SELECT CAST(CONVERT(varchar, CURRENT_TIMESTAMP, 112) AS int)

You could put this in the UDF (probably at least as fast as your current
Calenmdar-table based function), or use it inline as a replacement to the
function call (probably even faster).

It should work for all dates from Jan 1st 1753 through Dec 31st 9999.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)