Extracting Unusual Date/times
Jan 18, 2008
I'm having a bit of trouble with dates/times. All I want to do is extract the contents of a table in an old Informix database and load it in to table in a SQL 2005 database.
The problem is that when I try to extract from the source table, I get the following error:
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E07 Description: "Error converting data type DBTYPE_DBTIMESTAMP to datetime.".Error: 0xC004701A at Dump Dacom Tables, DTS.Pipeline: component "mac_header" (181) failed the pre-execute phase and returned error code 0xC0202009.
It would have been nice if it could tell me what columns it was having trouble with. Anyway, I figured it out. There are two columns called time_open and time_close that contain times but no dates. This is how the source columns are configured:
time_open datetime hour to minutetime_close datetime hour to minute
This is a sample of what's in it:
select time_open, time_close from mac_headertime_open time_close07:07 07:0711:07 11:0707:07 07:0711:07 11:0706:55 06:55
This is a really old database. We started using it about 14 years ago. I can get an ODBC connection to it fine with any application except SQL Server. So, I have to do an OPENROWSET to get access to the data.
SELECT * FROM OPENROWSET('MSDASQL', 'Connection String Stuff', 'select * from dacom.mac_header')
Question 1: Why can't I get an ODBC connection to this Informix 5.10 database?
Question 2: How can I extract tables with this sort of data in it? I'm happy to store it as a normal date/time field in the destination SQL 2005 database, but I can't convert something that I can't extract.
Thanks
David Kirk
View 3 Replies
ADVERTISEMENT
Jun 16, 2005
Hi,
does anyone know how to extract the date (dd.mm.yyyy) from a date expression with format (dd.mm.yyyy hh:mm:ss) ?
Thx in advance
jolli
View 3 Replies
View Related
Dec 2, 2005
Is there a way to extract the time from a field like this
2005-11-14-14:30:00:000
I just want to use the 14:30 out of that field but cant seem to find the right way to do it. I can use datename and get the hour but i need it to the min.
thanx
View 2 Replies
View Related
Oct 13, 2007
Hi, im currently learning sql. I have a problem extracting a date from a table.
SELECT Date FROM Flight
Gives me a "syntax error in sql expression"
in the table the date coloum - fields are set Date/Time
an example field is 28.12.2007 00:00
Thanks in advanced!
Steve
p.s. im using openoffice base, if i force to run the code it display's the date. however I would like it to run without errors. Thanks again
View 4 Replies
View Related
Mar 7, 2014
I want to extract day of the week from given date.
If the given date is 07-03-2014(dd-mm-yyyy).IF i run the query the output should be =Friday
View 2 Replies
View Related
Dec 31, 2013
I have a request where i would like to get the start date/time and end date/time and flag (with an int) which hours (24 hour clock) have values between the two dates. Example car comes into service on 2013-12-25 at 0800 and leaves 2013-12-25 at 1400 the difference is 6 hours and i need my table to show
Column: Hour_6 Value: 0
Column: Hour_7 Value: 0
Column: Hour_8 Value: 1
Column: Hour_9 Value: 1
Column: Hour_10 Value: 1
Column: Hour_11 Value: 1
Column: Hour_12 Value: 1
Column: Hour_13 Value: 1
Column: Hour_14 Value: 0
As i'm working away at it i'm trying to figure out how i could use a Time Dimension table for this but dont really see much. So far i have the difference between the two times in hours (hour_diff) and the start hour (min_hour) so i would like to do something where i update the first hour (min_hour) and update columns based on the numbers of hours (hour_diff)
View 9 Replies
View Related
May 31, 2015
I have a table that stores working hrs, such as
RecID,StaffID,StartDate,EndDate
17,969,2015-05-18 00:00:00.000,2015-05-18 06:00:00.000
18,969,2015-05-18 18:00:00.000,2015-05-19 06:00:00.000
19,969,2015-05-19 18:00:00.000,2015-05-20 06:00:00.000
20,969,2015-05-20 18:00:00.000,2015-05-21 06:00:00.000
21,969,2015-05-21 18:00:00.000,2015-05-22 06:00:00.000
22,969,2015-05-22 18:00:00.000,2015-05-23 06:00:00.000
23,969,2015-05-23 14:00:00.000,2015-05-24 08:00:00.000
24,969,2015-05-24 22:00:00.000,2015-05-25 00:00:00.000
So working times can go over midnight, there can be more than one working period in a day etc.
For this staff member the summary of the weeks work will be
18/05/2015 - 12 hrs
19/05/2015 - 12 hrs
20/05/2015 - 12 hrs
21/05/2015 - 12 hrs
22/05/2015 - 12 hrs
23/05/2015 - 16 hrs
24/05/2015 - 10 hrs
Now for the complicated part, a person can take absence(sick,holiday,other) for any part of a day or whole day(s). For these absence periods only the worked time on that day needs to be negated off, not the whole period of time.
So for example
If this person
had a days holiday on the 22nd, shown in the HOLIDAY table as
StaffID,DateFrom, DateTo
969, 22/05/2015 00:00:00.000,22/05/2015 23:59:59.000
A Leave of Absence on the 20th, shown in the LEAVE table as
StaffID,DateFrom, DateTo
969,20/05/2015 12:00:00.000,20/05/2015 16:00:00.000
And was off sick on the morning of the 19th, shown in the SICKNESS Table as
StaffID,DateFrom, DateTo
969, 19/05/2015 00:00:00.000,19/05/2015 11:59:59.000
Now the Summary table should now show
18/05/2015 - 12 hrs
19/05/2015 - 6 hrs
20/05/2015 - 12 hrs
21/05/2015 - 12 hrs
22/05/2015 - 0 hrs
23/05/2015 - 16 hrs
24/05/2015 - 10 hrs
The 'Leave of Absence' on the 20th had no effect on the total for the day as it was between planned work times. how to do this within T-SQL, as simple as possible as I've got to had this code over to other staff members to maintain, who have not had much SQL experience yet?
I've tried doing it as a temp table, with dual insert/select commands, splitting the times over midnight, which partially worked but missed some of the combinations.
View 1 Replies
View Related
Jul 20, 2005
SQL Server 2000I need to compare 2 tables as follows:Table 1 - AccountsAcct#, Account NameTable 2 - Ledger(Among Others)Acct#,AccountNameI would like to create a view where the account number matches in both tables but the account name does not.I.e. Table 1:5000 MaintenanceTable 2:5000 Maintenance5000 Maintenance5000 Building Maintenancemy query view would display:5000 Building Maintenanceindicating there is a "Bad Record" and I would do some processing from there.FYI - I know I could prevent this easily, but I am actually comparing several databases with Table 2 data with a central Table 1 database.TIA-- Tim Morrison--------------------------------------------------------------------------------Vehicle Web Studio - The easiest way to create and maintain your vehicle related website.http://www.vehiclewebstudio.com
View 2 Replies
View Related
Aug 30, 2006
First, I can not change the data structure. I have a table called codesubsections that has an identity as the primary key. When a new codesubsection is entered the user can mark a formercodesubsection as a parent. The problem is that I need to be able to evaluate the table and identify the lowest CodeSubsectionID for a given row. so for instant when I look at row 7 it's text is 17 B p2 but because the CodeSubSectionID is not null I need to look at 6 then 5 and since 5 is null I need the text for 5,6,7 all to be 16:1 B. The only solution I have had any kind of luck with is to do a self join 3 times and coalesce the values up. See the code at the end. I am at my wits end, I had it working the other way assuming that the CodeSubSectionID was the parent of the record. This is the final piece of code on a project that is supposed to end tomorrow.
Any help is appreciated. I would normally post the procedures but I think that they are more confusing that the data explanation. This type of relationship is a first for me after 20 some years.
ID CodeSubSectionID Text
1 NULL 16:1 A
2 NULL 16:1 B
3 NULL 17 A
4 NULL 17 B p1
5 NULL 16:1 B
6 5 16:1 B
7 6 17 B p2
8 3 17 C
9 NULL 18
View 7 Replies
View Related
Oct 3, 2000
We're trying to construct a query that deletes records containing 48 particular phone numbers from a large db. The 48 numbers are the entire contents of a smaller db, in a field of the same name as in the larger db (home_phone). We're using Sequel Server 7.0 and Access 97. The db's are in Access now. We failed in Acess and now would like to import into sql and try it there.
Thanks,
Tad McArdle
View 1 Replies
View Related
Jun 9, 1999
I'm running SQL Server 6.5 (SP5a) on an NT4 server (SP3). I am using the Outlook Express account for SQLExecutiveCmdExec
which has all the requisite permissions. SQL Executive as well as SQL Server services are logging in under this account.
The server seems to be functioning normally in every other way (there's no production data on it yet).
When attempting to start SQL Main, the following lines appear in the Errorlog
1999/06/09 16:15:16.62 ods Starting SQL Mail session...
1999/06/09 16:15:16.64 ods Error : 17951, Severity: 18, State: 1
1999/06/09 16:15:16.64 ods Failed to start SQL Mail session.
1999/06/09 16:15:16.64 ods OS Error : 126, The specified module could not be found.
MSKB Does not have any information on this error message. Can anyone provide some insight as to what's going on please?
P.S. I have another server running SQL Mail with the same configuration with no problem.
View 2 Replies
View Related
Jul 20, 2007
For my website, I need to grab information from sources located on different servers. This will give me me various datasets that I need to combine into one, main dataset to then bind to my grid. Each dataset has the following fields:
Name || Blocked
I need the main dataset that is to be used on the grid to have the following structure:
Name || Blocked 1 || Blocked 2 || Blocked 3 || etc.......
The name field I can take from one of the datasets as the list of fields will be the same. However, I need to take the data from each of the blocked fields in the various datasets that are returned and then combine them to be the Blocked 1, etc columns that you can see above.
I'm making this post to ask for any advice on the best way to do this. Any info would be most appreciated.
Thanks,
James
View 11 Replies
View Related
Apr 21, 1999
I recently renamed a database with sp_renamedb and created a new db with the same name as the old and restored the data into the new db. I put the old renamed db in single user mode to prevent data from becomming violated. After cycling SQL Server to flush the cache, I opened the floodgates for users. Our support line was full of calls from people saying "I'm getting the error 'Single User Mode in database DBORGINALDBNAME'. Any ideas why the application would be hitting the old database after I renamed and recycled the server?
View 1 Replies
View Related
Sep 23, 2005
Hi all,
I'm Trying to replicate the creation of an "xml" file that is currently created using a C++ application. I want to take that application out of the picture, but need to create the same format XML file because a step later in the production process uses this file, and I cannot change it.
The output format I am looking for is:<?xml version="1.0" encoding="utf-8"?>
<FUNDS>
<AMRGX>
<NAME>AMERICAN GROWTH D</NAME>
</AMRGX>
<AHERX>
<NAME>AMERICAN HERITAGE FUND</NAME>
</AHERX>
<AMRVX>
<NAME>AMERICAN INVESTORS GROWTH FUND</NAME>
</AMRVX>
.
.
.
</FUNDS>The problem I am having is that I cannot seem to get the level/node of the fund symbol (AMRGX, AHERX, and AMRVX in the example above) as it needs to be. I think this must be some non-standard use of XML, since the tag is really the data itself (?)
The closest I have been able to get so far is:
<FUNDS>
<SYMBOL>AMRGX</SYMBOL>
<NAME>AMERICAN GROWTH D</NAME>
</FUNDS>
<FUNDS>
<SYMBOL>AHERX</SYMBOL>
<NAME>AMERICAN HERITAGE FUND</NAME>
</FUNDS>
.
.
.As you can see (hopefully) I am able to get the data I need but cannot get:
(1) the FUNDS tag(s) to be the very highest level/root.
nor (2) the SYMBOL part (tag label?) to be the actual variable stock fund.
Am I 'splaining this well enough? I don't necessarily need all the code, since I know I haven't given enough info to help with that, but my basic question is - - Is it possible to get a variable TAG based on the table DATA?
I want my SYMBOL tag to be the actual SYMBOL for the stock fund.
Confused? Not as much as I am *LOL* I am new to the use of all but XML EXPLICIT use, so any help would be appreciated - at least regarding my two formatting questions.
Yes, I have (and am still) searching around BOL for my answers, but so I have found nothing that helps me out. Meanwhile, suggestions are welcome!
Thanks!
View 6 Replies
View Related
Nov 21, 2006
I was trying to restore a transaction log when I got this.
What does this mean? I can't find anything by Googling on the
'fixed' parts of the message.
The file 'xx' cannot be moved by this RESTORE operation
View 2 Replies
View Related
Feb 7, 2008
This is regarding one package where we are trying to deploy the package through €œSql Server deployment€? using .dtsx, .dtsConfig and manifest files, but after deployment the package is not found in €œmsdb€?. Instead it is reflecting in €œfile system€? folder. The same behavior is observed repeatedly when we tried to deploy the package.
We have seen such behavior only in this package. Please help us in solving the above scenario.
View 1 Replies
View Related
Mar 20, 2006
Have a column with the following dataa
fname.lname
fname1.lname1
Now I need to seperate the fname & lastname into 2 different columns how to do this
please help
thanks
View 2 Replies
View Related
Oct 29, 2002
hello,
I'm running my user database on a SQL Server 2000. I would like to extract all the indexes from the database and I was wondering, how can i do that. I dont see an option for doing this.
Appreciated If someone out there help me please.
Thanks in advance.
View 6 Replies
View Related
Jun 17, 2004
hi
I want to select all the dates between 2 dates.ie if i'm passing 17th June and 19th june then it shud give 17,18 and 19.
Like wise if i'm giving 28th june and 3rd july it shud return 28,29,30,1,2,3.
Pls help me.
Thnx in advance
Nebu
View 1 Replies
View Related
Sep 17, 2004
I really apprciate any idea's
On extracting ddl's for each object on a separate file in SQL Server?
Something like ddl's for:
procedure1.sql, procedure2.sql.....so on procedure100.sql
onto a file server.
Does SQL Server has any API's like Oracle's dbms_metadata?
Or can we use SQL enterprise manager to do this job?
Thanks, Madhavi.
View 3 Replies
View Related
Apr 26, 2007
I am trying to extract just the session id from some iislogs, and was curious the best way to go about doing this. The session ids will all be like "ASP.NET_SessionId=1wtdqn241f0ews55n5hsipib" with 42 chars in length, however the sessionid can be anywhere in the column, sometimes by itself, sometimes at the beginning and sometimes near the end. I guess the best way to do this is search for %ASP.NET_% but im not sure how to pull just the sessionid out.
does anyone know how I should approach this or where this topic has been brought up in the forums so I could get an idea. I tried searching by instr and sessionid and few others.
Thanks
SAMPLES WHAT THE DATA MIGHT LOOK LIKE
ASP.NET_SessionId=3mhnrf45wwiypi45polih5ey
ASP.NET_SessionId=ejsfrueqay4tuo45iuszsy45;+.RELYFORMSAUTH=C81CBCADBC5DEB801A3D4EBBC187A063CFCB70D678C1419BBFBE49B06CEE0039EBC85A9592882C15E47B69BD2FBE82A570B3F93011EA95B905301FC547DE67E68D3B5EB0E53CA824EC271A0B4EF8092E
.OASIS7FORMSAUTH=34720D24F742723CD786480BC252E8ABE392B2EC146FC1474501C3BC2A8126FF0539AD35BB14D0B259F366289611B1C696724932DE0C792AAD60B3867430EB8D3540F7B36991A851924F465A5E00CC2B;+ASP.NET_SessionId=3mhnrf45wwiypi45polih5ey
View 8 Replies
View Related
Jul 10, 2000
I have a field in sqlserver 7.0 defined as type datetime. It has the following value - 2000-07-01 00:44:01.700. What is the best way to strip only the 2000-07-01 out of the field into another field as a date?
View 2 Replies
View Related
Feb 6, 2001
hi all,
i have a device file which i know for sure contains all the data for a database (not the log). is there any way to recover the data from this file?
thanx.
:-)
hemant
View 2 Replies
View Related
Sep 11, 2004
hi all
i want to extract only the duplicate values from a table
can any one know the sql syntax for that
thnking u
jag
View 2 Replies
View Related
May 22, 2006
The situation is I need info from a certain SharePoint site that is in a sql database. The problem is The SharePoint site currently contains data from 9 different projects, all using the same database. The database is a complex setup and it would not be an easy operation to extract the special information alone. The database is run with Microsoft SQL and is comprised of 4 separate databases. 3 databases organize the data (500+mb of data total) and 1 stores the data for all the sites (12.5+GB and increasing at a rate of 100-300mb per day).
How can I get the special data I need from this setup???
Please help
View 3 Replies
View Related
Jul 9, 2007
Hi everybody i need help on on a query on how i can extract this... with the following table below..
id pub
1 a
1 b
2 c
2 c
I need to extract only the id and pub where pub has more than one with the same id... in the case of the above the result would be
id pub
2 c
2 c
thanks
View 2 Replies
View Related
Mar 27, 2008
I want to extract a particular portion of primary key field data.For example- suppose there are fifty data in PK field starting from 1...50.Now I want only to extract data from 25 to 50.It is bcos I want to retrieve only the new data that is inserted after a specified time.For example-today there is data starting from 1 to 25 which I have retrieved and saved elsewhere.By tomorrow there will be new data starting from 26 to 50 or more.Now I just want to extract this additional data from the database so that I dont need to extract all data again.Could anybody pls tell me how to do this.How can I do it using common SQL bcos I have to make a program for it.
View 1 Replies
View Related
May 19, 2004
Cheers
I would really apprecite if anyone could make a comment on this...
what i wanna know is....
there are two tables, called 'A' and 'B' with identical structures...
is it possible to Perform the following in a single SQL ....
* If A->Field1 = B->Field1 then A->Field2 + b->Field2
* Records of table 'A' which not in table 'B' and
* Records of table 'B' which not in table 'A'
Thanks
View 5 Replies
View Related
May 21, 2008
Hey all
Is it possible to extract length(size) from an image?
Many thanks,
Rupa
View 13 Replies
View Related
Nov 8, 2013
I have to extract only the filename from the below filepath.
The first part prodserverexport always remains the same. A new folder is created prodserverexport 20071001, prodserverexport 200710012 etc each time the file is dropped . I want to extract only the part after that namely 20071001-200001-e430f8fd-PREC272.wma and like wise and dump it into a table.
prodserverexport2007100120071001-200001-e430f8fd-PREC272.wma
prodserverexport2007100220071002-013203-d00b2007-PREC120.wma
prodserverexport2007100220071002-032038-18381b92-PREC207.wma
prodserverexport2007100820071008-144758-16359a7b-PREC224.wma
prodserverexport2007100920071009-170426-4ed3535c-PREC97.wma
View 7 Replies
View Related
Jan 31, 2014
I am trying to write a query that selects multiple rows of a table and puts those into a single string with a comma delimiter.
I want it to select all rows after a specific word is found in the row above and then stop selecting when another specific word below the last row is found.
Example below: >>>
The data i am using is very unorganised and has not headers, so I have to try and specific lines in order to allocate them to a specific user.
Sample Data:
IDFullContent
459Authorized Privileges:
460ACNT ALLSPOOL ALTPRI AUDIT BUGCHK BYPASS
461CMEXEC CMKRNL DIAGNOSE DOWNGRADE EXQUOTA GROUP
462GRPNAM GRPPRV IMPERSONATE IMPORT LOG_IO MOUNT
463NETMBX OPER PFNMAP PHY_IO PRMCEB PRMGBL
464PRMMBX PSWAPM READALL SECURITY SETPRV SHARE
465SHMEM SYSGBL SYSLCK SYSNAM SYSPRV TMPMBX
466UPGRADE VOLPRO WORLD
467Default Privileges:
Output Expected:
"
ACNT,ALLSPOOL,ALTPRI,AUDIT,BUGCHK,BYPASS,CMEXEC,CMKRNL,DIAGNOSE,DOWNGRADE,EXQUOTA,
GROUP, GRPNAM,GRPPRV,IMPERSONATE,IMPORT,LOG_IO,MOUNT,NETMBX,OPER…etcc"
The delimiter can be anything.
View 1 Replies
View Related
Aug 31, 2005
Hey guys
Im pretty new to SQL so i was wondering if you might be able to give me a hand with something.
I have 2 tables, clients + orders. Each table contains a column called custID which contains the client code, each order creates a new record in the orders table so the orders table contains many more records than the clients table.
I am trying to find out what clients have not placed orders at all, but i can't wrap my head around it, i can obviously work out who HAS, but i need to find out who HASN'T.
Any help would be really appreciated.
Mark
View 4 Replies
View Related
Aug 22, 2006
Hello,
I have a field in my table that includes free form text. Within this text are two five digit numbers seperated by a forward slash (/). I need to be able to locate the "/" and then return and display the numbers before and after the "/" seperately. For example:
"the text looks like this but has two numbers 55555/66666 within the string"
I need to get the "55555" and the "66666" in oprder to then display them. can anyone help? I am using ASP/SQL. Appreciated in advance!
View 5 Replies
View Related
