Combining Time with a Date

Let's say that I have two tables, one of which has a StartDate field (say,
TableA) as well as a foreign key reference to TableB which has a field
called BeginTime. Suppose that values in TableA.StartDate field have varying
dates together with a time of 00:00:00.000 (as it's a datetime field) and
TableB.BeginTime fields have varying time values (e.g. 07:00:00.000). NOTE:
TableB.BeginTime field is currently an nvarchar field.
What I want to do is compare the current date (using GETDATE()) to the
datetime value that results from combining TableA.StartDate with
TableB.BeginTime. For example, if TableA.StartDate = "03/29/2005
00:00:00.000", and TableB.BeginTime = "07:00:00.000", I want to compare the
current date to "03/29/2005 07:00:00.000". I will ultimately be trying to
determine if the difference between them is greater than a certain # of
minutes. How could I do this using SQL?Bob
Look at DATEDIFF system function.
"BobRoyAce" <bob@.decisioncritical.com> wrote in message
news:%23byP4xCNFHA.3844@.TK2MSFTNGP14.phx.gbl...
> Let's say that I have two tables, one of which has a StartDate field (say,
> TableA) as well as a foreign key reference to TableB which has a field
> called BeginTime. Suppose that values in TableA.StartDate field have
varying
> dates together with a time of 00:00:00.000 (as it's a datetime field) and
> TableB.BeginTime fields have varying time values (e.g. 07:00:00.000).
NOTE:
> TableB.BeginTime field is currently an nvarchar field.
> What I want to do is compare the current date (using GETDATE()) to the
> datetime value that results from combining TableA.StartDate with
> TableB.BeginTime. For example, if TableA.StartDate = "03/29/2005
> 00:00:00.000", and TableB.BeginTime = "07:00:00.000", I want to compare
the
> current date to "03/29/2005 07:00:00.000". I will ultimately be trying to
> determine if the difference between them is greater than a certain # of
> minutes. How could I do this using SQL?
>|||I am familiar with the DATEDIFF function, but that will not combine separate
DATE and TIME values together to give me a DATETIME. That's the piece I'm
missing here.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u1zyYHDNFHA.3076@.tk2msftngp13.phx.gbl...
> Bob
> Look at DATEDIFF system function.
>
> "BobRoyAce" <bob@.decisioncritical.com> wrote in message
> news:%23byP4xCNFHA.3844@.TK2MSFTNGP14.phx.gbl...
> varying
> NOTE:
> the
>|||Since you express datetime as a string, it is just a matter of building a st
ring expression which
can safely be converted to datetime. I didn't follow your first post, but le
ts assume that one value
is datetime and the other is a string:
DECLARE @.a datetime, @.b nvarchar(40)
SET @.a = getdate()
SET @.b = '07:00:00'
SELECT CAST(CONVERT(char(8), @.a, 112) + ' ' + @.b AS datetime)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"BobRoyAce" <bob@.decisioncritical.com> wrote in message
news:%23mQ5VNDNFHA.244@.TK2MSFTNGP12.phx.gbl...
>I am familiar with the DATEDIFF function, but that will not combine separat
e DATE and TIME values
>together to give me a DATETIME. That's the piece I'm missing here.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:u1zyYHDNFHA.3076@.tk2
msftngp13.phx.gbl...
>|||What's the 112 for?|||>> What's the 112 for?
It is the argument for CONVERT to return the ISO format( yymmdd ) for dates
represented as a string. See the topic CAST and CONVERT in SQL Server Books
Online.
Anithsqlsql

Combining the dates

Hi Guys

I have got two columns- one is the year and the other is the month number. I need to combine these two columns so that they form a date

For Ex

Year Month CombinedColumn

2000 11 2000/11

2003 01 2003/01

I am using SQL Server 2005

Thanks

I'll assume that Year and Month are integers and CombinedColumn is of data type DATETIME. If other data types are used, this solution might not apply.

Code Block

CREATE TABLE #Temp

(

[Year] INT NOT NULL,

[Month] INT NOT NULL,

[CombinedCol] DATETIME NULL

)

INSERT INTO #Temp VALUES (2000, 11, NULL)

INSERT INTO #Temp VALUES (2003, 01, NULL)

UPDATE #Temp

SET [CombinedCol] = DATEADD(month, [Month] - 1, DATEADD(year, [Year] - 1900, '1900-01-01 00:00:00.000'))

SELECT * FROM #Temp

|||

you can try this..

select cast( (cast(yearValue as varchar(4))+cast(monthValue as varchar(2)) + '01') as datetime)

you will get the date as the first day of your year and month values...

|||

Harish has the right idea, but it should be noted that 2000/11 is not a valid date. You can just concatenate them for display, but as for making them a date, can you explain more what you will do with the dates once you have them concatenated? (unless just making them the first day of the month suffices, of course

|||

My query would be like select cast('20001101' as datetime) which sql server implicitly converts to datetime value from varchar if it is in yyyymmdd format. The user needs to decide which value he needs for the dd value in the string

|||

Here it is,

Code Block

Create Table #sampledata (

[Year] varchar(4),

[Month] varchar(2)

);

Insert Into #sampledata Values('2000','11');

Insert Into #sampledata Values('2003','01');

DECLARE @.UserDay varchar(2);

Set @.UserDay = '5'

Select Convert(Datetime, Year + Month + substring(cast((cast(@.UserDay as int) + 100) as varchar),2,2),112) [Output] from #sampledata

|||

Another method with fewer keystrokes

Code Block

SELECT CAST(LTRIM([Year] * 10000 + [Month] * 100 + @.UserDay) AS DATETIME)

FROM #sampledata

|||Is it bad to use implicit conversion? I believe it will be faster than explicitly handling the conversion.|||

Well, I think it is better to use explicit conversion. That way you leave no room for anyone for interpretation and avoid any ambiguities.

|||

Just a note: "Partial Dates" like "January, 1968" are not really supported. Basically in SQL, if you really want to work with dates, you must store a full date...."January 1, 1968" for example. SQL will infer the time component as being Midnight and store it as "01 Jan 1968 00:00" (if you're using smalldatetime) and "01 Jan 1968 00:00:00.000" using DateTime.

So, for your query, let's add a day-of-the-month component:

Code Block

select convert(smalldatetime,'01' + '/' + Month + '/' + Year) as ThisIsTheDate from

or...if the values are stored as numbers (which is not suggested in your example of "01" which would probably be "1" if it were stored as a number:

Code Block

select convert(smalldatetime,"01/" + right('00',convert(varchar,month),2) + '/' + convert(varchar,year))

from

|||

Thanks .It works fine

Cheers

|||

Actually, here's one that I like better:

Note that DateAdd(year,50,0) = 'January 1, 1950' and DateAdd(month, 5, 0) = 'June 1, 1900' so....

Code Block

CREATE TABLE #Temp

(

[Year] INT NOT NULL,

[Month] INT NOT NULL,

[CombinedCol] DATETIME NULL

)

INSERT INTO #Temp VALUES (2000, 11, NULL)

INSERT INTO #Temp VALUES (2003, 01, NULL)

select dateadd(month, [Month]-1,dateadd(Year,[year]-1900,0))

from #Temp

|||

I came up with the same approach, but after you. One little thing...rather than specify the "base date" with a string of "1900-01-01 00:00:00.000") you can simply use the numeric 0

Code Block

select DateAdd(month, 10, 0)

select DateAdd(month, 10, "1900-01-01 00:00:00.000")

Combining or Concating seperate fields for date?

I have two tables in a SQL db.
Each has 3 separate fields used to store a date info:

lastservicemonth tinyint1
lastserviceday tinyint1
lastserviceyear smallint2
(and these fields can be nulls)

I want to compare the date info in Table A vs. Table B and find the latest date between the two.

I know I somehow need to combine the 3 separate fields in each table to form one date field. Then I can compare the dates.
But ths far I have been unsuccessful.

Any help would be greatly appreciated!To get the latest row, use:SELECT TOP 1 *
FROM [Table A]
ORDER BY lastserviceyear, lastservicemonth, lastserviceday-PatP|||Thanks Pat - I can can see how my question was unclear. Hope this clarifies.

Say Table A and Table B both contain the same person records for when they last came in the hospital. But the tables contain different dates.

Example:
Table A - ID #123, John Doe , 11-1-2007
Table B - ID#123, John Doe, 12-3-2007

I want to update the date fields in Table A with the data in Table B, but only if the date in Table B is more recent than the date in Table A.

So, I need to compare the dates for each person in Table A to the same person in Table and determine which visit date is more recent.

Hope this makes more sense.|||Why are two tables storing such similar information?

If you give us the real problem, it might also be easier to decipher than "table A and table B..."|||While I understand the desire to "simplify" a problem for posting purposes, the process usually infuriates me... All too often critical pieces of information get "simplified" out of the example that gets posted!

Can you post at least the DDL for the tables (in other words the CREATE TABLE statements needed to recreate them), and whatever attempt you've made so far to do what you want? This would help us a lot in determining what you need.

-PatP|||I appreciate your feedback. I can see I need to clarify.
Table A and B are in different dbs connected to different apps.
The apps communicate with each other imperfectly, so the dates get out of synch.

I imported the id# and the day, month and year column from System B, Table B to system A.
Now I want to update Table A with that data.

Here's a select statement where I attempt to identify discrepancies in the two data sets. If I can correct this , I can do an update statement. The statement below adds the 3 date fields and arrives a a number rather than a date. Do the fields need to be converted from smallint and tinyint ?

SELECT
(p.lastserviceYEAR +'-'+ p.lastserviceMONTH +'-'+ p.lastserviceday) as pdate,
(e.lastserviceYEAR +'-'+ e.lastserviceMONTH +'-'+ e.lastserviceday) as edate
From patient p Inner Join empi e On (p.ID = e.ID)
where edate > pdate|||I'd use something like:Cast(1000 * lastserviceyear + 100 * lastservicemonth + lastserviceday AS INT)to get integer values that you can safely compare and sort... They aren't pretty to print, but they work well for comparisons and sorting.

-PatP|||I was going to suggest the use of DateAdd(), but I think that will be far more efficient.|||last time it was me dropping a zero, pat, this time it's you ;)

Cast(10000 * ...|||Excellent. That will do the trick.
Thanks to all for your help and patience!

Combining date field and time field in a column

SELECT RequireDate + ' ' + RequireTime AS dat
FROM IN_Heade
My Database
Date Tim
28/03/2004 01:34:09P
After run SQL statement, my result become
26/03/2004 01:34:09P
Why my date minus two day? so what should i need to do? Urgent please reply to me at Babies001@.yahoo.co
ThankWhat datatypes are you using for storing your date and time values? If using
strings, then concatenate them and use CAST or CONVERT functions to convert
the concatenated value into a datetime value.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"TM" <anonymous@.discussions.microsoft.com> wrote in message
news:E6A4D56F-97F0-4E97-AD2D-3348504128DF@.microsoft.com...
SELECT RequireDate + ' ' + RequireTime AS date
FROM IN_Header
My Database:
Date Time
28/03/2004 01:34:09PM
After run SQL statement, my result become:
26/03/2004 01:34:09PM
Why my date minus two day? so what should i need to do? Urgent please reply
to me at Babies001@.yahoo.com
Thanks|||Regarding the Question, my database fiel
Field DataTyp
Date DateTim
Time DateTim
So my result become
SELECT RequireDate + ' ' + RequireTime AS dat
FROM IN_Heade
My Database
Date Tim
28/03/2004 01:34:09P
After run SQL statement, my result become
26/03/2004 01:34:09P
What the code for convert the date and time together and my date will not minus two day
Can adding the source code inside
Thank
-- Narayana Vyas Kondreddi wrote: --
What datatypes are you using for storing your date and time values? If usin
strings, then concatenate them and use CAST or CONVERT functions to conver
the concatenated value into a datetime value
--
HTH
Vyas, MVP (SQL Server
http://vyaskn.tripod.com
Is .NET important for a database professional
http://vyaskn.tripod.com/poll.ht
"TM" <anonymous@.discussions.microsoft.com> wrote in messag
news:E6A4D56F-97F0-4E97-AD2D-3348504128DF@.microsoft.com..
SELECT RequireDate + ' ' + RequireTime AS dat
FROM IN_Heade
My Database
Date Tim
28/03/2004 01:34:09P
After run SQL statement, my result become
26/03/2004 01:34:09P
Why my date minus two day? so what should i need to do? Urgent please repl
to me at Babies001@.yahoo.co
Thanksqlsql

Combining date and time

I have 2 fields, one containing just the date and another containing the time. I want to combine them into a proper date/time format. No matter what I try, I lose 2 days in the process. Combining

2005-12-21 00:00:00.000
and
1899-12-30 14:30:00.000

will get me the 19th at 14:30. What am I missing here?

TIAThis was addressed recently in the following thread: link (http://www.dbforums.com/t1203973.html)

Regards,

hmscott|||Thank you. I searched and somehow missed that thread. Bad choice of search words I suppose. I guess my problem was not using varchar. I had tried using convert but used datetime as the type, using a style of 101 for the date portion and 108 for the time. This seems to be working correctly:

Convert(datetime,Convert(varchar(11), DateField, 101) + ' ' + Convert(varchar(8), ReqTime, 108))

Combining 2 tables with date ranges

Hi there, I'm trying to generate a report for an old database and I'm
having trouble coming up with an elegant way of going about it. Using
cursors and other 'ugly' tools I could get the job done but 1) I don't
want the report to take ages to run, 2) I'm not a big fan of cursors!

Basically there are tables that track history and each table tends to
track only a specific value housed within a date range. I'm trying to
combine the tables to get a snap-shot of the complete history. I'm
having problems dealing with the Start/End Dates from the two tables
and building the dates in the final table to be broken down by 'history
type'.

Here are a few sample records and the results I'm trying to achieve:

Table 1:
CAgyHist (ProdID,AgyID,StartDate,EndDate)
1 1 Jan 1, 2006 Jan 5, 2006
1 2 Jan 5, 2006 Jan 25, 2006
1 1 Jan 25, 2006 NULL

Table 2:
CInvHist (ProdID, InvID,StartDate,EndDate)
1 1 Jan 1, 2006 Jan 23, 2006
1 2 Jan 23, 2006 Jan 15, 2006
1 1 Jan 15, 2006 NULL

Desired End Result:
CTotalHist (ProdID,AgyID,InvID,StartDate,EndDate)
1 1 1 Jan 1, 2006 Jan 5, 2006
1 2 1 Jan 5, 2006 Jan 15, 2006
1 2 2 Jan 15, 2006 Jan 23, 2006
1 2 1 Jan 23, 2006 Jan 25, 2006
1 1 1 Jan 25, 2006 NULL

My challenge thus far has been dealing with the dates as they don't
necessarily correspond - from one table to the other.

I am by no means a database expert of any level and any help would be
greatly appreciated.

Thanks,
Frank.what do you mean by , "the dates don't correspond from 1 table to the
other"?

--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________

"Frank" <mrpubnight@.hotmail.com> wrote in message
news:1151369612.360817.191930@.c74g2000cwc.googlegr oups.com...
> Hi there, I'm trying to generate a report for an old database and I'm
> having trouble coming up with an elegant way of going about it. Using
> cursors and other 'ugly' tools I could get the job done but 1) I don't
> want the report to take ages to run, 2) I'm not a big fan of cursors!
> Basically there are tables that track history and each table tends to
> track only a specific value housed within a date range. I'm trying to
> combine the tables to get a snap-shot of the complete history. I'm
> having problems dealing with the Start/End Dates from the two tables
> and building the dates in the final table to be broken down by 'history
> type'.
> Here are a few sample records and the results I'm trying to achieve:
> Table 1:
> CAgyHist (ProdID,AgyID,StartDate,EndDate)
> 1 1 Jan 1, 2006 Jan 5, 2006
> 1 2 Jan 5, 2006 Jan 25, 2006
> 1 1 Jan 25, 2006 NULL
> Table 2:
> CInvHist (ProdID, InvID,StartDate,EndDate)
> 1 1 Jan 1, 2006 Jan 23, 2006
> 1 2 Jan 23, 2006 Jan 15, 2006
> 1 1 Jan 15, 2006 NULL
> Desired End Result:
> CTotalHist (ProdID,AgyID,InvID,StartDate,EndDate)
> 1 1 1 Jan 1, 2006 Jan 5, 2006
> 1 2 1 Jan 5, 2006 Jan 15, 2006
> 1 2 2 Jan 15, 2006 Jan 23, 2006
> 1 2 1 Jan 23, 2006 Jan 25, 2006
> 1 1 1 Jan 25, 2006 NULL
> My challenge thus far has been dealing with the dates as they don't
> necessarily correspond - from one table to the other.
> I am by no means a database expert of any level and any help would be
> greatly appreciated.
> Thanks,
> Frank.|||>From your data, CInvHist has this row

CInvHist (ProdID, InvID,StartDate,EndDate)
1 2 Jan 23, 2006 Jan 15, 2006

which has StartDate *after* the EndDate. Is this what you mean?|||It looks like you want to treat the 2 tables as one so you can sort by
the start date? If so, then you can use a union query and use the order
by clause at the end of the second select statement like:
select * from table1
union
select * from table2
order by start date

Jason|||Frank (mrpubnight@.hotmail.com) writes:
> Basically there are tables that track history and each table tends to
> track only a specific value housed within a date range. I'm trying to
> combine the tables to get a snap-shot of the complete history. I'm
> having problems dealing with the Start/End Dates from the two tables
> and building the dates in the final table to be broken down by 'history
> type'.
> Here are a few sample records and the results I'm trying to achieve:
> Table 1:
> CAgyHist (ProdID,AgyID,StartDate,EndDate)
> 1 1 Jan 1, 2006 Jan 5, 2006
> 1 2 Jan 5, 2006 Jan 25, 2006
> 1 1 Jan 25, 2006 NULL
> Table 2:
> CInvHist (ProdID, InvID,StartDate,EndDate)
> 1 1 Jan 1, 2006 Jan 23, 2006
> 1 2 Jan 23, 2006 Jan 15, 2006
> 1 1 Jan 15, 2006 NULL
> Desired End Result:
> CTotalHist (ProdID,AgyID,InvID,StartDate,EndDate)
> 1 1 1 Jan 1, 2006 Jan 5, 2006
> 1 2 1 Jan 5, 2006 Jan 15, 2006
> 1 2 2 Jan 15, 2006 Jan 23, 2006
> 1 2 1 Jan 23, 2006 Jan 25, 2006
> 1 1 1 Jan 25, 2006 NULL
> My challenge thus far has been dealing with the dates as they don't
> necessarily correspond - from one table to the other.

There should be a fair chance to this in a query (or possibly two
with help of some temp table). But since it's bit complex, the hour
is late, and your sample data is unclear, I prefer to ask for
clarification:

1) What are the keys of these tables?
2) What do they signify?
3) What is the combined table supposed to describe?
4) Is that interval from Jan 23 to Jan 15 intentional or is a typo?
In the latter case, can you provide an updated sample?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Sorry everyone there was a typo and I will expand a little as well.

1) The keys are as follows (both tables have primary ID keys too but
they weren't included in the original question - see brackets below)
CAgyHist:
(CAH_ID PK)
ProdID FK
AgyID FK

CInvHist:
(CIH_ID PK)
ProdID FK
InvID FK

2) ProdID = PK from the products table.
AgyID = PK from the Agency table (i.e. Supplier)
InvID = PK from the InventoryType table (categorization for products)

3) Products in our application can move from supplier to supplier and
can also change their categorization. Each of the history tables
tracks these changes as they occur and when they occur. The start date
is obviously when the product begins with the corresponding agency or
categorization, and the end date is when it finishes (a NULL value
means that the product is still with a given agency or being
categorized in a certain manner.

The problem I want/need to solve is I need a complete historical
account for a product as it moves from agency to agency and from
categorization to categorization and I need it to be on a single report
(table) and chronological, so hence the final table which shows how the
product has moved throughout time.

4) Yes, sorry that was a typo. The CInvHist table records should have
read:

Table 2:
CInvHist (ProdID, InvID,StartDate,EndDate)
1 1 Jan 1, 2006 Jan 15, 2006
1 2 Jan 15, 2006 Jan 23, 2006
1 1 Jan 23, 2006 NULL

Sorry about all that confusion. I'm really hoping that this isn't too
tough or time consuming (from an execution point of view).

Again, any help will be appreciated.

Thanks,
Frank

Erland Sommarskog wrote:
> Frank (mrpubnight@.hotmail.com) writes:
> > Basically there are tables that track history and each table tends to
> > track only a specific value housed within a date range. I'm trying to
> > combine the tables to get a snap-shot of the complete history. I'm
> > having problems dealing with the Start/End Dates from the two tables
> > and building the dates in the final table to be broken down by 'history
> > type'.
> > Here are a few sample records and the results I'm trying to achieve:
> > Table 1:
> > CAgyHist (ProdID,AgyID,StartDate,EndDate)
> > 1 1 Jan 1, 2006 Jan 5, 2006
> > 1 2 Jan 5, 2006 Jan 25, 2006
> > 1 1 Jan 25, 2006 NULL
> > Table 2:
> > CInvHist (ProdID, InvID,StartDate,EndDate)
> > 1 1 Jan 1, 2006 Jan 23, 2006
> > 1 2 Jan 23, 2006 Jan 15, 2006
> > 1 1 Jan 15, 2006 NULL
> > Desired End Result:
> > CTotalHist (ProdID,AgyID,InvID,StartDate,EndDate)
> > 1 1 1 Jan 1, 2006 Jan 5, 2006
> > 1 2 1 Jan 5, 2006 Jan 15, 2006
> > 1 2 2 Jan 15, 2006 Jan 23, 2006
> > 1 2 1 Jan 23, 2006 Jan 25, 2006
> > 1 1 1 Jan 25, 2006 NULL
> > My challenge thus far has been dealing with the dates as they don't
> > necessarily correspond - from one table to the other.
> There should be a fair chance to this in a query (or possibly two
> with help of some temp table). But since it's bit complex, the hour
> is late, and your sample data is unclear, I prefer to ask for
> clarification:
> 1) What are the keys of these tables?
> 2) What do they signify?
> 3) What is the combined table supposed to describe?
> 4) Is that interval from Jan 23 to Jan 15 intentional or is a typo?
> In the latter case, can you provide an updated sample?
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Your sample data is a mess, but the usual way is to build a calendar
and join these improperly designed tables together with BETWEEN
predicates, something like:

SELECT C.cal_date, T1.a, T2.b, ..
FROM Calendar AS C, T1, T2
WHERE C.cal_date BETWEEN T1.start_date AND T1.end_date
AND C.cal_date BETWEEN T2.start_date AND T2.end_date
AND .. ;

MIssing or reversed data will not be shown in this query.|||Frank (mrpubnight@.hotmail.com) writes:
> 1) The keys are as follows (both tables have primary ID keys too but
> they weren't included in the original question - see brackets below)
> CAgyHist:
> (CAH_ID PK)
> ProdID FK
> AgyID FK
> CInvHist:
> (CIH_ID PK)
> ProdID FK
> InvID FK

That's a bit problematic. It s not clear whether I can trust whether
ProdID, StartDate can be unique, or whether there can be more entries for
the same day and product. In my solution below, I have assumed they are
unique. Then again, if they were there is no reason for that CAH_ID.

Here is a query that works with your sample data. I will have to admit
that I'm not fully certain on how it works, and I would recommend you
to test further. I would also suggest that you check out
http://groups.google.com/group/comp...48dda4c48fb808b
for a similar problem.

CREATE TABLE CAgyHist (ProdID int NOT NULL,
AgyID int NOT NULL,
StartDate datetime NOT NULL,
EndDate datetime NULL,
PRIMARY KEY(ProdID, StartDate))

CREATE TABLE CInvHist (ProdID int NOT NULL,
InvID int NOT NULL,
StartDate datetime NOT NULL,
EndDate datetime NULL,
PRIMARY KEY(ProdID, StartDate))

INSERT CAgyHist(ProdID,AgyID,StartDate,EndDate)
SELECT 1, 1, 'Jan 1, 2006', 'Jan 5, 2006'
UNION
SELECT 1, 2, 'Jan 5, 2006', 'Jan 25, 2006'
UNION
SELECT 1, 1, 'Jan 25, 2006', NULL

INSERT CInvHist (ProdID, InvID,StartDate,EndDate)
SELECT 1, 1, 'Jan 1, 2006', 'Jan 15, 2006'
UNION
SELECT 1, 2, 'Jan 15, 2006', 'Jan 23, 2006'
UNION
SELECT 1, 1, 'Jan 23, 2006', NULL

SELECT ProdID, AgyID, InvID, StartDate, EndDate
FROM (SELECT a.ProdID, a.AgyID, i.InvID,
CASE WHEN a.StartDate > i.StartDate
THEN a.StartDate
ELSE i.StartDate
END AS StartDate,
CASE WHEN coalesce(a.EndDate, '99991231') <
coalesce(i.EndDate ,'99991231')
THEN a.EndDate
ELSE i.EndDate
END AS EndDate
FROM CAgyHist a
JOIN CInvHist i ON a.ProdID = i.ProdID) AS x
WHERE StartDate < coalesce(EndDate, '99991231')
ORDER BY StartDate, EndDate
go
DROP TABLE CAgyHist
DROP TABLE CInvHist

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Combine two stored procedure results

Hi,
I have two databases DB2006, DB2005.I have the Stored Procedure getdata which has the 2 parameters startdate and end date.This Stored procedure exist in all databases.

STored procedure CallGetdata
@.startdate datetime
@.enddate datetime
If startdate < 1/1/2007 the call getdata in the DB2006
if startdate <1/1/2006 then call getdata in the DB2005.
Here the problem is if startdate is 6/1/2005 and Enddate is '3/1/2006' then combine the stored procedure results from the DB2006 and DB2005 databases.
I have one idea i.e create a temp table and insert the two Stored procedure results into it.
Create #table1(name varchar(20))
insert into #table1 exec DB2006.dbo.getdata
insert into #table1 exexc DB2005.dbo.getdata
Select * from #table1
drop table #table1.
Anyone please give me better idea than creating temp table.

Thanks in advance

Can you change getdata (or is this something your stuck with?)

Another possible option is to turn your getdata stored procedure into a table valued function. This means you won't be able to exec it directly though, you will have to access it in a query. Then you could do:

select * from db2006.dbo.getdata(...) union select * from db2005.dbo.getdata(...).

This would most likely perform better (although I can't say for sure). If getdata() is one query (or can be turned into one query), then you can use an inline table valued function, and this will definitely perform better then the temp table solution.

Also, your probably better off using a table variable in this case as opposed to a temp table.

|||

if you are stuck with the stored procedure as-is, then your idea is the best one. Unless the proc is extremely complex (and with a name like getdata, it is not going to be easy for us to guess :) then building a database for queries that span databases is a better idea and union the results together. Or consider the ideas that Adam has given also.

I would consider not having databases with the year in the name, and just have one database that spans years, personally. That is clearly the most solid answer and will make your reporting easier. If this wasis a performance idea, there are ways to make this work far better than with multiple databases. And if both databases are on the same drive, you are possibly not saving much...

|||

Thank you very much for your ideas.For the reports the stored procedures already created.But now to imrove the performance they created the separate 3 databases one for current year and other for previous year and remaining(all previous years are in Hist databases).Now I need to migrate the existing stored procedure to all databases all working fine but the problem is when they enter startdate which is in one year and end date in another year, in this case we need to combine the results of two stored procedures from two databases.Thatswhy I created a separate stored procedure and temp table is used for combining the two SP results.

Thanks

|||

Hi,

Which one give better performance whether the Stored procedure with table datatype to insert the combined results from two databases, Or table valued functions.

Thanks.

Combine two queries - help please

I have a table that has two dates in it, a date opened and a date
closed. I would like to create one query to give me the number of
records that have been opened each month plus, and this is the hard
part the number of those records that have been closed each month. I
can get the result with two seperate queries but have been unable to
get it combined into one query with three values for each month, i.e.,
the month, the number opened and the number of those that were opened
in the month that have been subsequently closed.

Here's my two queries. If anyone can help I'd appreciate.

SELECT COUNT(*) AS [Number Closed], LEFT(DATENAME(m, DateOpened),
3) + '
' + CAST(YEAR(DateOpened) AS Char(5)) AS [Month Opened]
FROM table
WHERE (DateClosed IS NOT NULL)
GROUP BY CONVERT(CHAR(7), DateOpened, 120), LEFT(DATENAME(m,
DateOpened), 3)
+ ' ' + CAST(YEAR(DateOpened) AS Char(5))
ORDER BY CONVERT(CHAR(7), DateOpened, 120)

SELECT COUNT(*) AS [Number Opened], LEFT(DATENAME(m, DateOpened),
3) + '
' + CAST(YEAR(DateOpened) AS Char(5)) AS [Month Opened]
FROM table
GROUP BY CONVERT(CHAR(7), DateOpened, 120), LEFT(DATENAME(m,
DateOpened), 3)
+ ' ' + CAST(YEAR(DateOpened) AS Char(5))
ORDER BY CONVERT(CHAR(7), DateOpened, 120)

TIA

BillTry:

SELECT MIN(dateopened),
COUNT(*),
COUNT(dateclosed)
FROM YourTable
GROUP BY YEAR(dateopened), MONTH(dateopened)

--
David Portas
SQL Server MVP
--|||David;

Thank you very much that works just fine. I appreciate the help.

Cheers;

Bill

combine two columns

have a basic Q.
I have a table which contains two columns
froz_month and froz_year (yes the date has been split by the app into these
two)
I need to be able to "combine" these two back into one
like mmyyyy or yyyymm
I do not know what the proper sql statment is
I tried select froz_month + froz_year AS totdate
clearly that add's it together rather then giving me a combination
can anyone please clue me in on this
thanks
billBill
Lookup CONVERT () system function in the BOL
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:70F915F2-7119-4F9C-BD1D-5E4FA9ED58B7@.microsoft.com...
> have a basic Q.
> I have a table which contains two columns
> froz_month and froz_year (yes the date has been split by the app into
> these
> two)
> I need to be able to "combine" these two back into one
> like mmyyyy or yyyymm
> I do not know what the proper sql statment is
> I tried select froz_month + froz_year AS totdate
> clearly that add's it together rather then giving me a combination
> can anyone please clue me in on this
> thanks
> bill
>|||try...
select convert(varchar,froz_month ) + convert(varchar,froz_year) as totdate
from TABLE
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:70F915F2-7119-4F9C-BD1D-5E4FA9ED58B7@.microsoft.com...
> have a basic Q.
> I have a table which contains two columns
> froz_month and froz_year (yes the date has been split by the app into
> these
> two)
> I need to be able to "combine" these two back into one
> like mmyyyy or yyyymm
> I do not know what the proper sql statment is
> I tried select froz_month + froz_year AS totdate
> clearly that add's it together rather then giving me a combination
> can anyone please clue me in on this
> thanks
> bill
>sqlsql

combine separate date & time fields into one datetime field?

Good morning.

I am importing an XLS file into one of my tables. The fields are:

Date Id Time IO

12/22/2006

2

12:48:45 PM

9

12/22/2006

16

5:40:55 AM

1

12/22/2006

16

12:03:59 PM

2

When I do the import, I get the following:

Date Id Time IO
12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2
12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1
12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2

Here are my doubts:

1. Is it be better to combine the Date & Time fields into one column? Advantages/Disadvantages?
2. If I don't combine them, should I use varchar or datetime data type?
2. What issues or problems might I have when I program SQL reports, if I leave the fields as they are?

Any comments or suggestions will be very much welcomed.

Cheers mates.I was suggested to try this out:

UPDATE tbl
SET Date = Date + convert(char(8), Time, 108)

I'll run it after I use DTwizard to export the data into my table. I should also mention I have no PKs defined, just a FK that references Id from a table called Employees. I'm thinking it's best to define ID and Date and Time as PKs.|||

As far as SQL Server is concerned, it will be far easier over time to work with and deal with date/time data if it is stored as one column. I recommend combining the two.

One primary reason is that the combined column will allow easier datetime comparisions and searches.

combine separate date & time fields into one datetime field?

Good morning.

I am importing an XLS file into one of my tables. The fields are:

Date Id Time IO
12/22/2006 2 12:48:45 PM 9
12/22/2006 16 5:40:55 AM 1
12/22/2006 16 12:03:59 PM 2

When I do the import, I get the following:

Date Id Time IO
12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2
12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1
12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2

Here are my doubts:

1. Would it be better to combine the Date & Time fields into one
column? If so, how?
2. What issues or problems might I have when I program SQL reports, if
I leave the fields as they are?

Any comments or suggestions will be very much welcomed.

Cheers mates.drurjen (jfontecha@.gmail.com) writes:

Quote:

Originally Posted by

Good morning.
>
I am importing an XLS file into one of my tables. The fields are:
>
Date Id Time IO
12/22/2006 2 12:48:45 PM 9
12/22/2006 16 5:40:55 AM 1
12/22/2006 16 12:03:59 PM 2
>
When I do the import, I get the following:
>
Date Id Time IO
12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2
12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1
12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2
>
Here are my doubts:
>
1. Would it be better to combine the Date & Time fields into one
column? If so, how?

Most probably. (In the end it depends on business needs, which I don't
anything about.)

A way to merge the columns would be:

UPDATE tbl
SET Date = Date + convert(char(8), Time, 108)

Quote:

Originally Posted by

2. What issues or problems might I have when I program SQL reports, if
I leave the fields as they are?

That you get 1899-12-30 printed all over the place, which you probably
don't want to. So you will need a lot of code to filter the date away.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,

Thank you for replying. Basically the DB is for employee time
attendance records. I start out with a flat txt file and run that
through an Excel macro that:

a) eliminates repeat entries in a time lapse of 5min
b) erases null entries.

I then take the XLS file and use DTWizard to export it into a table
with the same fields as before: Date, Id, Time, IO. I have no primary
keys defined in this table, just a FK (Id). I believe the primary keys
should be ID, Date & Time.

I'll try your suggestion. Thx again, and sorry for the repeat post.

Combine raw files using range

HI, I have a dataflow that has two raw files as source and I would like to merge them upon a range condition:

RawFile1.Date <= RawFile2.Date

Usually, using tables, I would have used a lookup with partial cache to achieve it. Now, since we cannot use lookup transform with raw files, I was wondering how I could achieve this using raw files as source. Is it possible to merge raw files using merge or merge join?

Thank you,

Ccote

Sure, you can merge two raw files. You can merge any two data flow streams. The data just has to be sorted first.

You could load a staging table for each raw file, though, and write a SQL join against them to get your results as well...|||

ccote wrote:

HI, I have a dataflow that has two raw files as source and I would like to merge them upon a range condition:

RawFile1.Date <= RawFile2.Date

Usually, using tables, I would have used a lookup with partial cache to achieve it. Now, since we cannot use lookup transform with raw files, I was wondering how I could achieve this using raw files as source. Is it possible to merge raw files using merge or merge join?

Thank you,

Ccote

This appears to be a problem that requires SQL, meaning that both data sets need to be stored in tables. As suggested previously, the data sets can then be joined to yield the desired result set.

I hope this helps.

|||

Thank you both for your inputs. I will have to revise the design of this package. I wanted to get rid of the table solution since SSIS is installed on an application server (different than SQL server). By using raw files, I would be able to do all the work locally and at the end send the result to SQL server target table in one pass. I guess merge join are not flexible enough for now, maybe MS will had non equijoin fucntionnality in future version.

Thank you,
Ccote

|||

ccote wrote:

I guess merge join are not flexible enough for now, maybe MS will had non equijoin fucntionnality in future version.

New feature requests may be submitted here: SQL Server Feedback|||Can you explain what you mean to accomplish by "merge them upon a range condition"?|||

ccote wrote:

Thank you both for your inputs. I will have to revise the design of this package. I wanted to get rid of the table solution since SSIS is installed on an application server (different than SQL server). By using raw files, I would be able to do all the work locally and at the end send the result to SQL server target table in one pass. I guess merge join are not flexible enough for now, maybe MS will had non equijoin fucntionnality in future version.

Thank you,
Ccote

Yes. The lack of non-equi-join functionality really annoys me. I once enquired why it was not there and was told "nobody has asked for it". Well...it seems people are now asking for it.

A connect submission for this already exists: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126376 so please please please click-through, vote and ADD A COMMENT (just voting is next to useless - they need to see real reasons why this needs to be implemented). You can link back to this thread as well.

-Jamie

|||

HI, I would like to be able to do basically the same thing as I can do when I join tables: Be able to join on col1 <= col2. One way to achieve this would be being able to to <= (or <>, <, >, etc.) when I use merge join transform. Or,by being able to lookup against raw files, currently, we can only use lookups transforms against reelational tables. If we could be able to do lookups against raw files, it would be perferct since lookups can have parameters and we can specify the query by using ranges.

Ccote

Combine raw files using range

HI, I have a dataflow that has two raw files as source and I would like to merge them upon a range condition:

RawFile1.Date <= RawFile2.Date

Usually, using tables, I would have used a lookup with partial cache to achieve it. Now, since we cannot use lookup transform with raw files, I was wondering how I could achieve this using raw files as source. Is it possible to merge raw files using merge or merge join?

Thank you,

Ccote

Sure, you can merge two raw files. You can merge any two data flow streams. The data just has to be sorted first.

You could load a staging table for each raw file, though, and write a SQL join against them to get your results as well...|||

ccote wrote:

HI, I have a dataflow that has two raw files as source and I would like to merge them upon a range condition:

RawFile1.Date <= RawFile2.Date

Usually, using tables, I would have used a lookup with partial cache to achieve it. Now, since we cannot use lookup transform with raw files, I was wondering how I could achieve this using raw files as source. Is it possible to merge raw files using merge or merge join?

Thank you,

Ccote

This appears to be a problem that requires SQL, meaning that both data sets need to be stored in tables. As suggested previously, the data sets can then be joined to yield the desired result set.

I hope this helps.

|||

Thank you both for your inputs. I will have to revise the design of this package. I wanted to get rid of the table solution since SSIS is installed on an application server (different than SQL server). By using raw files, I would be able to do all the work locally and at the end send the result to SQL server target table in one pass. I guess merge join are not flexible enough for now, maybe MS will had non equijoin fucntionnality in future version.

Thank you,
Ccote

|||

ccote wrote:

I guess merge join are not flexible enough for now, maybe MS will had non equijoin fucntionnality in future version.

New feature requests may be submitted here: SQL Server Feedback|||Can you explain what you mean to accomplish by "merge them upon a range condition"?|||

ccote wrote:

Thank you both for your inputs. I will have to revise the design of this package. I wanted to get rid of the table solution since SSIS is installed on an application server (different than SQL server). By using raw files, I would be able to do all the work locally and at the end send the result to SQL server target table in one pass. I guess merge join are not flexible enough for now, maybe MS will had non equijoin fucntionnality in future version.

Thank you,
Ccote

Yes. The lack of non-equi-join functionality really annoys me. I once enquired why it was not there and was told "nobody has asked for it". Well...it seems people are now asking for it.

A connect submission for this already exists: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126376 so please please please click-through, vote and ADD A COMMENT (just voting is next to useless - they need to see real reasons why this needs to be implemented). You can link back to this thread as well.

-Jamie

|||

HI, I would like to be able to do basically the same thing as I can do when I join tables: Be able to join on col1 <= col2. One way to achieve this would be being able to to <= (or <>, <, >, etc.) when I use merge join transform. Or,by being able to lookup against raw files, currently, we can only use lookups transforms against reelational tables. If we could be able to do lookups against raw files, it would be perferct since lookups can have parameters and we can specify the query by using ranges.

Ccote

Comapring date fields

I am trying to compare two date fields; one is a string and one is a getdate() field. I am trying to get them into the same format so I can compare them. What am I doing wrong? :eek:

select convert(integer, substring(loc_86, 1, 2)) as tmonth,
convert(integer, substring(loc_86, 3, 2)) as tday,
convert(integer, '20'+right(loc_86, 2)) as tyear,
datepart(month, (dateadd(day, -1, (getdate())))) as ymonth,
datepart(day, (dateadd(day, -1, (getdate())))) as yesterday,
datepart(year, (dateadd(day, -1, (getdate())))) as yyear
from ub_chg_tbl join ubmast_tbl
on (ubmast_tbl.patient_nbr = ub_chg_tbl.patient_nbr)
where tmonth = ymonth and tday = yesterday and ty= yyeartry this as a template:


declare @.str char(06)
select @.str = '060228' -- Feb 28 2006
select convert(datetime,@.str)|||How about

where convert(varchar(10), dateadd (dd, -1, getdate()), 101) = loc_86

Depending on your delimiter, of course.|||How closely do you want to compare them? Getdate() returns results in milliseconds. Are you just trying to match on the day?|||I'm only trying to match the day.

When I use this script:

Convert(VarChar(12),GetDate(),112) as '112'

I get a date in a format of YYMMDD.

I need the date in the format of MMDDYY. How can I do this?|||Open BOL. Search the index for CONVERT. Read the topic "CAST and CONVERT". All the formats are given there.|||There is not a format listed for MMDDYY. Is this really not possible? :shocked:|||You should NOT be storing date values as strings. Possibly the most common noob DBA mistake of all time. I strongly urge you to change your datetype to datetime.

That said, this should work for you:select *
from YourTable
where datediff(day, getdate(), convert(datetime, left(DateString,2) + '-' + substring(DateString, 3, 2) + '-' + right(DateString,2), 10)) = 0|||I wish we could change it. We are in healthcare and this database is federally regulated, so we are not allowed to change the format of the fields.|||Now I am receiving this error:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.|||Because you have an invalid date in your table, because you are not using datetime datatypes.

Format your datestring as 'YYYY-MM-DD' and run it through the ISDATE() function to find the bad records.|||There is not a format listed for MMDDYY. Is this really not possible? :shocked:SELECT Replace(Convert(VARCHAR(10), GetDate(), 1), '/', '')-PatP|||Now I am receiving this error:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.As Sallah once told Indy: Bad Dates.

The following shows how to weed them out relatively painlessly:CREATE TABLE #patp_date_demo (patp_date CHAR(6))

INSERT INTO #patp_date_demo (patp_date)
SELECT '122505' UNION
SELECT '022900' UNION
SELECT '022901' UNION
SELECT '063104' UNION
SELECT '131211'

SELECT patp_date
FROM #patp_date_demo
WHERE 0 = IsDate(Stuff(Stuff(patp_date, 5, 0, '-'), 3, 0, '-'))

DROP TABLE #ptp_date_demo-PatP|||Thanks so much! The following script worked:

REPLACE(CONVERT(varchar(10), DATEADD(day, - 1, GETDATE()), 1), '/', '') AS Yesterday

Column Totals / Sum by Date

HI,
I am new to RS and I am running into some problems create reports. What I
would like to do is create a report that will count all distinct rows for a
"Users" column for every single date. I am able to get the total users from
the "Users" column but the problem is getting a running list of totals by
date. There is no date field in the database. There is a date field for
enrollment and unenroll but these are not the dates I am looking for. I
would like every single date to be totaled.
Do you know of any examples of this on the web or an example that you could
send me?
Any help would be great!
ThanksYou can't report on anything that's not in your data source. It sounds like
you're trying to get a count of users for each date. You'll have to solve
that in your source query first, then you can report on it. My suggestion
is to create a new reference date table with a record for each date in the
range you want to report on. That's only 365 records per year, so make as
many years as you want. Then join to the Users table on ReferenceDate
between EnrollDate and UnenrollDate.
A nice benefit of having a reference date table is that you can put other
data in each record as well, such as week number, quarter, fiscal year and
calendar year, for easy grouping. Yeah, it's denormalized, but makes
reporting a snap. It's easy to do this is in Excel, then import the data
into SQL.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"ACD" <ACD@.discussions.microsoft.com> wrote in message
news:66C00F58-9E89-456B-9931-5E2E5E964645@.microsoft.com...
> HI,
> I am new to RS and I am running into some problems create reports. What
> I
> would like to do is create a report that will count all distinct rows for
> a
> "Users" column for every single date. I am able to get the total users
> from
> the "Users" column but the problem is getting a running list of totals by
> date. There is no date field in the database. There is a date field for
> enrollment and unenroll but these are not the dates I am looking for. I
> would like every single date to be totaled.
> Do you know of any examples of this on the web or an example that you
> could
> send me?
> Any help would be great!
> Thanks

column name change

Hi, I'm trying to change the column name - date to Sdate in all the tables in my database. As i have many to change so i tried to search all tables and have it change automatically rather than manually however my query doesn't seem to do the job? requesting assistance from anyone is appreciated thank you!

DECLARE @.sSQL AS VarChar(500), -- SQL Statement
@.sTableName AS VarChar(100) -- TableName

DECLARE CursorTable CURSOR FOR SELECT [NAME] FROM SYSOBJECTS WHERE XTYPE ='U'

OPEN CursorTable
FETCH NEXT FROM CursorTable INTO @.sTableName

WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sSQL = 'IF EXISTS(SELECT * FROM SYSOBJECTS OBJ ' +
'INNER JOIN SYSCOLUMNS COL ON OBJ.ID = COL.ID ' +
'WHERE OBJ.XTYPE= ''U'' AND OBJ.NAME = ''' + @.sTableName + ''' AND COL.NAME = ''DATE'') ' +
' BEGIN ' +
'ALTER TABLE ' + @.sTableName + ' ADD TRANDATE DATETIME' +
'UPDATE ' + @.sTableName + ' SET TRANDATE=[DATE]' +
'ALTER TABLE ' + @.sTableName + ' DROP COLUMN DATE' +
'PRINT ''' + @.sTableName + ' DATE Exist''' +
' END'
EXEC (@.sSQL)

FETCH NEXT FROM CursorTable INTO @.sTableName
END

CLOSE CursorTable
DEALLOCATE CursorTable

-----------------------

SELECT [name] FROM Sysobjects WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1

--create CURSOR to Loop every table
IF (PATINDEX('%date%', [name]) > 0)
Begin
Print [name]
EndWhy not just use the command

ALTER TABLE t1 CHANGE date SDate DATETIME;

to change the name of the column in your table rather than creating a new column copying the data and deleting the old column.

Of course this is a MySql extension but you haven't said what database you are using.|||Thanks. I'm using MS SQL 2000
How do i get that extension converted to SQL 2K?
Not really good at MySql|||I realize this is new post to an old post, but when you are looking for solutions, it would be nice to see one that works simply.

I believe the simple solution to this problem is the stored procedure called:

sp_rename

to quickly rename a file with script do the following:

sp_rename [ @.objname = ] 'object_name' , [ @.newname = ] 'new_name'
[ , [ @.objtype = ] 'object_type' ]

USE AdventureWorks;
GO
EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';
GO

You can find more detailed info in books on-line. This works with MSSQL 2000 and 2005.

____________________
Keep it simple!

Column Heading in Crosstab

I have a crosstab that contains dates grouped by week as the column. Since it is grouped by week it gives Sunday as the start date of the week. Does anyone know how I can change it so that it will show Monday as the beginning of the week?Create a formula having this code
weekdayname(weekday({datefield})+1)
Now Group the report by this formula|||I tried this formula in my crosstab and it seperated the columns into Monday, Tuesday, Wednesday etc. I need it to show the dates. For example, what it shows across the top now is:

6/5/05 6/12/05 6/19/05 6/26/05

what I need it to show is the week starting on Monday:

6/6/05 6/13/05 6/20/05 6/27/05

Any help would be appreciated.

Coloring the mx value in a record set

Hi,
I have a simple report that displays several records having 2 fields, date
field and Quantity field
I ma,aged to alternate the colors of line (RosntBrown and black) bu I need
also to display the max Qty in a color different of the other ones, how this
can be achieved
ThanksHello eliassal,
You could add an Expression in the backgroundColor properties of the
textbox like this:
=IIF(ReportItems!Quantity.Value=MAX(Fields!Quantity.Value),"Black","Transpar
ent")
If is the max Qty, the background will be black and other will be
Transparent.
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||So many thankls, it works like a charm. Now, how about displaying different
colors for Max and Min values for the same text box. can we use 2 expressions
at the same time on the same textbox containing thye field.
Thanks
"Wei Lu [MSFT]" wrote:
> Hello eliassal,
> You could add an Expression in the backgroundColor properties of the
> textbox like this:
>
> =IIF(ReportItems!Quantity.Value=MAX(Fields!Quantity.Value),"Black","Transpar
> ent")
> If is the max Qty, the background will be black and other will be
> Transparent.
> Hope this will be helpful.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello eliassal,
Of course. You could do like this:
=IIF(ReportItems!Quantity.Value=MAX(Fields!Quantity.Value),"Black",IIF(Repor
tItems!Quantity.Value=min(fields!Quantity.Value),"Red","Transparent"))
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi, I was in vacation, I will check next week and let you know
Thanks
"Wei Lu [MSFT]" wrote:
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>