Need Help Moving Dates From One Table To Another Using A DTS...
May 17, 2004
I have a timestamp in one table that is of datetime type and I need to move it to a table with a Varchar(30) type. What is happening is a date that should be set up like this "11/12/2004" is actually coming across as "Nov 12 2004". I tried using a cast(fieldname) as Varchar(30) on the datetime type, but it didn't help. does anyone have any ideas on how to retain the original date form in moving it to a varchar type? thanks in advance...
View 6 Replies
ADVERTISEMENT
May 28, 2008
Hi,
I have created a formula that moves week dates (4 Weeks)(For header purposes). I want this to be applied in months. Is it possible?
Here's my formula:
SELECT DATEADD(DAY, DATEDIFF(DAY, '19000107', GETDATE()) / 14 * 14, '19000107') - 1 AS Week4,DATEADD(DAY, DATEDIFF(DAY, '19000107', GETDATE()) / 14 * 14, '19000107') - 15 AS Week3,DATEADD(DAY, DATEDIFF(DAY, '19000107', GETDATE()) / 14 * 14, '19000107') - 29 AS Week2,DATEADD(DAY, DATEDIFF(DAY, '19000107', GETDATE()) / 14 * 14, '19000107') - 43 AS Week1
View 20 Replies
View Related
Feb 28, 2015
I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.
CREATE TABLE hotel_guests
(
[guest_name] [varchar](25) NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[comment] [varchar](255) NULL
[code]...
View 7 Replies
View Related
Mar 29, 2007
Hi
I am having a table called as status ,in that table one field is there i.e. currentstatus.
the rows which are having currentstatus as "ticket closed",i want to move those rows into other table called repository which is having same table structure as status table.
I can do programatically.
but is there any way for every 3 months system has to check and do this action means moving to repository table automatically?
Please help me.
Thanks.
View 1 Replies
View Related
Sep 17, 2015
I cannot create a measure that returns results for dates that do not exist in the fact table despite the fact that the components included in the measure contain valid results for these same dates.Creature a measure that counts the number of days where the "stock qty" is below the "avg monthly sales qty for the last 12 months" (rolling measure).Here is the DAX code I have tried for the measure (note that filter explicitly refers to the date table (called Calendar) and not the fact table):
StkOutCnt:=CALCULATE (
COUNTROWS ( VALUES ( Calendar[DateKey] ) ),
FILTER (
Calendar,
[Stock qty] < [Avg Monthly Sales Qty L12M@SKU]
)
)
Below you can see the sub measures (circled in red) are giving results for all days in the calendar.Highlighted in yellow are dates for which the StkOutCnt measure is not returning a result. Having investigated these blank dates, I am pretty confident that they are dates for which there are no transactions in the fact table (weekends, public holidays etc...).why I am getting an "inner join" with my fact table dates despite the fact that this is not requested anywhere in the dax code and that the two sub measures are behaving normally?
View 6 Replies
View Related
Dec 20, 2007
can sql server do this ?
table 1 that check table 2 and adding missing dates
this my employee table
table 1
table Employee on work
------------------------
empid basedate shift
----------------------------
12345678 01/04/2007 1
12345678 02/04/2007 1
12345678 03/04/2007 1
12345678 04/04/2007 1
12345678 05/04/2007 1
12345678 06/04/2007 1
12345678 07/04/2007 1
12345678 08/04/2007 1
12345678 09/04/2007 1
12345678 10/04/2007 1
98765432 20/04/2007 1
98765432 21/04/2007 3
98765432 22/04/2007 3
98765432 23/04/2007 5
98765432 25/04/2007 4
98765432 26/04/2007 4
98765432 27/04/2007 4
98765432 28/04/2007 4
98765432 30/04/2007 4
-----------------------------------------------------------------------------------
and i need to see the missing dates lkie this
in table 2
------------------------------------------------------
table 2 (adding missing dates with zero 0)
table Employee_all_month
------------------------
empid basedate shift
----------------------------
12345678 01/04/2007 1
12345678 02/04/2007 1
12345678 03/04/2007 1
12345678 04/04/2007 1
12345678 05/04/2007 1
12345678 06/04/2007 1
12345678 07/04/2007 1
12345678 08/04/2007 1
12345678 09/04/2007 1
12345678 10/04/2007 1
12345678 11/04/2007 0
12345678 12/04/2007 0
12345678 13/04/2007 0
12345678 14/04/2007 0
12345678 15/04/2007 0
12345678 16/04/2007 0
12345678 17/04/2007 0
12345678 18/04/2007 0
12345678 19/04/2007 0
12345678 20/04/2007 0
.................................and adding missing dates with zero 0 until the end of the month
.................................
12345678 31/04/2007 0
98765432 01/04/2007 0
98765432 02/04/2007 0
98765432 03/04/2007 0
98765432 04/04/2007 0
98765432 05/04/2007 0
98765432 06/04/2007 0
98765432 07/04/2007 0
98765432 08/04/2007 0
98765432 09/04/2007 0
..............................and adding missing dates with zero 0 only whre no dates in this month
.......................
98765432 20/04/2007 1
98765432 21/04/2007 3
98765432 22/04/2007 3
98765432 23/04/2007 5
98765432 25/04/2007 4
98765432 26/04/2007 4
98765432 27/04/2007 4
98765432 28/04/2007 4
98765432 30/04/2007 4
TNX
View 4 Replies
View Related
Jan 17, 2008
Hi all,
I have two tables within the SQL server and there is an application writes 20.000 or 40000 rows in the First table.
1) I need to know when the table completes filled to start move the data to the second table.
2) After I copy the rows I need to delete the rows from the first table.
I though to use the trigger but I am not sure if this is the best approach or not
Please help....
Thank you
sms
View 11 Replies
View Related
Apr 6, 2007
I am trying to write a stored procedure that copies one row from one table and moves it to another table. The two tables do not have exactly the same number of columns and not all of the columns that are the same in each table have the same name. I am using MS SQL Server 2000 and I cannot figure out a way to do this.
My big question is how do you execute a query in a stored procedure and then use the results to execute an update in the same procedure. I want the entire function to occur without the client application having to do any of the work.
Thanks,
Julian
View 1 Replies
View Related
May 27, 1999
Hi all,
This is (probably) a simple question, but I'm new to SQL Server scripting.
What I want be able to do is move data from one table in a database to another table in the same database, once one of the fields (a date field) reaches a certain value.
Specifically, we are inserting rows into a table that are stamped with an insert date and an expiry date. When the expiry date is reached, we want to move the applicable row from the original table to an identically structured table.
Ideally, we want this to be a script that is run daily.
Does anyone have any ideas or examples that we could use.
Any help is much appreciated!
Tim
View 3 Replies
View Related
Aug 29, 2002
I need to know if it is possible to move ONE table from an existing filegroup to another existing filegroup.
The answer I received to use ALTER database only modifies the file/filegroup name or changes the default filegroup.
Any assistance will be greatly appreciated.
View 1 Replies
View Related
Nov 10, 2004
I imported a little over 9 million records into my database without "cleaning" the data.
I'm looking for suggestions on what would be the most efficient way to get all the fields to the correct types and to insert decimals on the fields that need them.
My only idea thus far is to make another table with the correct field types and append the data to the new table.
View 1 Replies
View Related
Aug 27, 2012
We currently have all tables in the dbo schema, but for organizational reason we would like to split them up in multiple schemas and I wonder if that can be done without re-creating the tables.
So my question is, is there a way to move a table into a different schema without re-creating it? (For those familiar with Postgres I'm looking for an equivalent to "ALTER TABLE foo SET SCHEMA newschema") sp_rename only allows a "one-part name" for the new name, so apparently that cannot be used.
View 2 Replies
View Related
May 8, 2014
Wondering if its possible to copy a table form one DB and paste to another dB? is there a tool for that?
View 7 Replies
View Related
Oct 23, 2007
I have a table with 200 million rows which needs to be moved to a different instance. What is the best way to move the table while at the same time getting rid of any unused space in the table? I think BCP is the fastest way to do this, but I am conrned that the results would be different than exporting the t-sql equivalent and running that.
Any comments? BOL does not appear to address this issue very clearly. . .
Thanks,
Michael
View 4 Replies
View Related
Oct 19, 2007
Hi all,
I am currently writing a web service that gets data from a MSSQL2000 DB and I need to get the data to create some drop down lists, but these lists are composed of many concatenated elements. To sort these alphabetically is proving to be a royal pain in the butt as they just come out as they are ordered in the DB.
It would be a whole lot easier if i could just reorder the rows in one relevant table. Does anyone know how to do this or if it is possible to move rows up or down?
Thanks a lot!
Richee.
View 22 Replies
View Related
Apr 9, 2008
Hi
I have a dataset, a sql query, that selects a bunch of opportunities. The result consist of close date, values, names, owners, topics and so on.
I have put the result in a matrix and the. The matrix will grow to the right with the months columns. Only the months that have an opportunity in them will be shown. (estimated close date is the date were I select from).
So, for example when there are opportunities in Jan, Feb and Aug in 2008 only those 3 months will show the data that corresponds to each opportunity.
Now, how can I make my query to show all the empty months as well? I want a matrix that shows Jan, Feb, Mar, €¦ Nov and Dec, even if they don€™t have any opportunities in them.
Kind Regards
View 1 Replies
View Related
May 1, 2008
Hi,
Here is my scenario simplified.
tbl1
userid, secondaryid
tbl2
userID, secondaryID
tbl1 is already populated. I just want to transfer the secondary ID to tbl2 based on the userID. I don't want to store secondaryID in tbl1 anymore.
View 4 Replies
View Related
Oct 20, 2013
I need to move data from one table to another using variable percentages and business days.
Here is the basic idea:
Variables: varTable1, varTable2, varPercentage
Get varPercentage of rows of VarTable1 that have a date of "current business date -1" and place into varTable2.
View 1 Replies
View Related
Mar 28, 2006
Hi All,
I have a view that contains 30 million records.I want to move the view to a table in my database using DTS,but it is taking a lot of time,and making my tempdb to grow fast in giga bytes.Please is there anyway i can copy this view into the table easily in minutes.The view structure and the table structure are the same.Also, how can I index a view and can I add unique key to a view.
Thanks All in advance.
Mokah
View 6 Replies
View Related
Oct 17, 2007
Hi all
I have a table called ACTIVATION_CONSUMPTION which is in PRIMARY file group, in order to move this table to new file group [FG_ACTV], I have done the following
1. ALTER DATABASE [MYDB] ADD FILEGROUP [FG_ACTV] (i have not attached a file to this file group)
2. IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ACTIVATION_CONSUMPTION]') AND name = N'ACTIVATION_CONSUMPTION_PK')
ALTER TABLE [dbo].[ACTIVATION_CONSUMPTION] DROP CONSTRAINT [ACTIVATION_CONSUMPTION_PK]
GO
3.ALTER TABLE [dbo].[ACTIVATION_CONSUMPTION] ADD CONSTRAINT [ACTIVATION_CONSUMPTION_PK] PRIMARY KEY CLUSTERED
(
[ACTIVATION_CONSUMPTION_ID] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [FG_ACTV]
The above command executed successfully and now the table is in new file group [FG_ACTV]
My question is how come the table was moved to new file group though it has no files attached to it?
Thanks in advance.
View 5 Replies
View Related
Oct 2, 2007
Is there anyway to move tables without any indexes to a filegroup?
View 1 Replies
View Related
Feb 7, 2013
We store all hearing dates in our db. Most cases have many hearings, but I only want to get the last two dates from the hearing table.
I can get the very last hearing date by using Max() and I can get the first by using Min() but how do I get the next previous date?
View 5 Replies
View Related
Feb 13, 2008
Hi Guys
how do I create a dates table which contains a list of all the possible dates in sql server 2005??
Thanks
View 4 Replies
View Related
Oct 25, 2006
Hi all,
I am trying to create to a store procedure that manages to return the last 10 dates from a table i know there is a TOP keyword that return the first rows but is there a keyword that returns the last rows instead?
Matt
View 3 Replies
View Related
Sep 25, 2003
In order to export data to a 3rd party provider, I build five separate tables to store the data. Every table has a different layout, except for the first four columns. They are record type, SSN, employee id and another id number.
Basically, I have to sort that data by SSN then by record type. Each employee will have multiple records.
However, that data will need to be "merged" into one table to be exported.
I have created a table that defines the first four columns, but then has one large "filler" field that will contain the rest of the data. How can I copy data from five different tables with five different layouts into one table?
Any suggestions?
Thanks,
Steve Hanzelman
View 6 Replies
View Related
May 19, 2008
How to move a table to a new file group when the table DOES NOT HAVE A CLUSTERED INDEX.
I also don't want to , create a new table, move the data, drop the old table.
Is there a easier way?
------------------------
I think, therefore I am - Rene Descartes
View 4 Replies
View Related
May 8, 2007
We have recently added a new file group and file on a new drive. We have tested it by moving a small table to the new file group. We would like to relocate a new table to this file group but the table is about (we estimate) 75GB. My question is this: How long can we expect the transfer of data from the current file group to the new one for this table? I understand that depending on our hardware the answer may vary but does anyone have a rough estimate?
The current current (primary) file is located on a DELL SAN and the new secondary group is on a EMC 4700 both are connected via fiber channel.
Also a bonus question would be: Does a "normal" database backup created as a maintenance plan backup the seconary data as well into the BAK file?
Like I said a rough estimate is fine.
Many thanks,
Scott
View 4 Replies
View Related
Nov 28, 2007
Hi,I have a employee attendance table, and Im trying to return the dates on which an employee is absent. There are no table entries for an employee who is absent on a particular day. So can anyone please help on how i can return the dates on which an employee is absent. Any help will be greatly appreciated, Thanks,Najla.
View 5 Replies
View Related
Aug 18, 2014
I've a request: I've a table with a date column and an if numeric data type.
I've to check from now on 7 days ago if any value is missing in my date field.
So if I've f.i. in my table
ID DAYS
1 2014-08-11
2 2014-08-12
3 2014-08-13
4 2014-08-14
5 2014-08-17
then my output should be:
2014-08-15
2014-08-16
2014-08-18
How to do this?
View 1 Replies
View Related
May 23, 2006
Hi, i have a table with 3 ints that are used to store dates. Thedatetime data type is not used because this data comes from an oldAS400 server.I need to be able to use those 3 columns to build dates within a queryand be able to use them to compare themselves to other datesLet's say the table has the following values:myday mymonth myyear23 5 2006and suppose i want to do a query that displays all rows with dategreater than '20060520'Here is the query i have tried:selectcast(myday as varchar(2))+'/'+cast(mymonth asvarchar(2))+'/'+cast(myyear as varchar(4))from mytableThat query returns the string '23/5/2006' yet i can't use it to compareit with '20060520'Is there a way i can do this in a simple query?This is on sql server 2000
View 6 Replies
View Related
May 30, 2006
How to move some tables with data & procedures etc from 1 database to another in sql server 2005 express edition.
i did by scripting but i transfer tables and procedures and not data
data is the problem.
tnx
View 1 Replies
View Related
Jul 20, 2005
I am using SQL Server 2000, SP3.I created an updatable partitioned view awhile ago and it has beenrunning smoothly for some time. The partition is on a DATETIME columnand it is partitioned by month. Each month a stored procedure isscheduled that creates the new month's table, and alters the view toinclude it. Again... working like a charm for quite some time.This past weekend I moved some of the first tables onto a new filegroup. I did this through Enterprise Manager, by going into designmode for the table, then going into the properties for the table andchanging the file group there as well as in all of the indexes. Nowthe partitioned view is no longer updatable. It gives the errormessage: "UNION ALL view '<view name>' is not updatable because apartitioning column was not found."I have extracted the DDL for all of the partition tables and comparedthem and they all look the same. I checked and then double-checked theCHECK constraints to make sure that they were all valid and they are.If I remove the tables that I moved to the new file group from theview, then it is once again updatable, but when I put them back in itfails again.Any ideas? If you would like samples of the code then I can send italong, but it's rather large, so I have not included it here.Thanks!Thomas R. Hummel
View 3 Replies
View Related
Jan 5, 2008
Hi,
I have an excel sheet in which there is some data in sheet1,sheet2.I need to transfer this 2 sheets data to single table using a single package.How can i do this in SSIS.
thanks in advance
Pradeep
View 4 Replies
View Related
