How To Combine Date And Time Before Import Data?
Aug 20, 2007
I have extracted the data from Point of Sales. it has two columns one for date and one for time in the database.
I need to combine both column into single column with the format "mm/dd/yyyy hh:mm:ss" before i import the data into the SQL server for my BI project.
Example: Data extract from Point of Sales
FDATEFTIME
20060114063616
20060115070743
20060116071020
How can i combine those two when i import into SQL server like below:
FDATE
01/14/2006 06:36:16
01/15/2006 07:07:43
01/16/2006 07:10:20
million thanks.
View 2 Replies
ADVERTISEMENT
Jan 27, 2012
We have a piece of software and database for student registers.
One of the biggest problems is the database has been difficult to work with, as I have had to work out the date using dateadd functions and combine fields from different tables.
I've now got the date correctly, but I want to add the time to the time part of a datetime field.
The time field is already stored in a datetime field, but the date in this field is always 1899-12-30.
This is the function I am using to get the date:
Code:
DATEADD(dd, e.day_of_week, DATEADD(ww, sm.week, '01/08/2011'))
And this is the function I use to get the time (from a datetime field):
Code:
SUBSTRING(CAST(e.start_time AS varchar(20)), 13, 5)
With the DATEADD function it appears you can only add individual elements each time hh, mi, ss.
Would I have to add a DATEADD function for each time element in order to add the time to the date?
View 2 Replies
View Related
Aug 28, 2007
adate atime
08-21-2007 11:09
08-20-2007 16:49
08-03-2007 00:39
I would like to combine adate with atime to get adatetime
View 5 Replies
View Related
Dec 26, 2006
Good morning.I am importing an XLS file into one of my tables. The fields are:Date Id Time IO12/22/2006 2 12:48:45 PM 912/22/2006 16 5:40:55 AM 112/22/2006 16 12:03:59 PM 2When I do the import, I get the following:Date Id Time IO12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 212/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 112/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2Here are my doubts:1. Would it be better to combine the Date & Time fields into onecolumn? If so, how?2. What issues or problems might I have when I program SQL reports, ifI leave the fields as they are?Any comments or suggestions will be very much welcomed.Cheers mates.
View 2 Replies
View Related
Dec 26, 2006
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.
View 3 Replies
View Related
Nov 13, 2007
Hi there.
I'm trying to extract data from my SQL server & everything in the script I've got is working (extracting correct data) except for one field - which is for the most part it's off by +2 days (on a few occasions - I see it off by just +1 day or even +3, but it's usually the +2 days).
I'm told that it's due to the conversion formula - but - since SQL is not my native language, I'm at a bit of a loss.
The DB table has the date field stored as a type: CHAR (as opposed to 'DATE')
Can anyone out there help?
Please advise. Thanks.
Best.
K7
View 1 Replies
View Related
Jun 30, 2007
This is driving me nuts..
I'm trying to extract some data from a table in oracle. The oracle table stores date and time seperately in 2 different columns. I need to merge these two columns and import to sql server database.
I'm struggling with this for a quite a while and I'm not able to get it working.
I tried the oracle query something like this,
SELECT
(TO_CHAR(ASOFDATE,'YYYYMMDD')||' '||TO_CHAR(ASOFTIME,'HH24:MM : SS')||':000') AS ASOFDATE
FROM TBLA
this gives me an output of 20070511 23:06:30:000
the space in MM : SS is intentional here, since without that space it appread as smiley
I'm trying to map this to datetime field in sql server 2005. It keeps failing with this error
The value could not be converted because of a potential loss of data
I'm struck with error for hours now. Any pointers would be helpful.
Thanks
View 3 Replies
View Related
Mar 29, 2015
I'm using OpenRowSet to import about 30 columns from a csv file with 190 columns with a format file. Ultimately, I want to put this in an SSIS Package. I am receiving the following error when trying to import date and decimal info.
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 990, column 64 (TOTALSALES). There are several similar errors. I looked at this line and it is 17873.34 so I am not seeing the problem. Every value in the column is either 0 or a 2 digit decimal value. If I change the SQL Column and format file to to NVARCHAR, it imports fine.
The existing format file and SQL Column looks as follows. There are multiple errors referring to different columns and all of them seem to be valid decimals. I am having the same issue with date fields that exist in the csv as 20130521. If I bring it in as text, it is fine.
<FIELD COLLATION="SQL_Latin1_General_CP1_CI_AS" MAX_LENGTH="12" TERMINATOR="," xsi:type="CharTerm" ID="64"/>
<COLUMN xsi:type="SQLDECIMAL" SCALE="2" PRECISION="15" NAME="TOTALSALES" SOURCE="64"/>
The SQL Column is defined as Decimal ((15,2), NULL))
I created a small csv file with representative decimal, date, integer and NVarchar fields and it imports into SQL fine as decimal and date info. The SQL Query used is pretty simple. Ultimately, I am planning to create a package that imports this data and joins to a production table based on values in the csv file. It will either update existing values in a Production Table or insert New Values
INSERT INTO Import.dbo.test1
SELECT *
FROM OPENROWSET(BULK 'C:ShareImport.csv',
FirstRow=2,
FORMATFILE='C:ShareImport.xml'
) AS t1;
I am assuming there is bad data in the csv file but I'm not sure how to identify it as my test file seems to bring in date values with a format of 20140923 and 2 digit decimal values and that is what exists in the line numbers being referenced. I've not used OpenRow Set for this purpose before. The only workaround I've found is to bring it all in as text and create additional fields so I can cast or convert the date values which I'd rather not do as this process seems to work in my small sample file.
View 1 Replies
View Related
Jun 3, 2014
I am new to sql database programming. developing an application using C# and sql server 2005. i am having problems with date insertion to database. I use datatimepicker control to get date input. here is my code. in table i use datetime column type.
strSQL = "Insert Into TB1 (PPT_No,Reference_No,Application_Date,Receipt_No,Citizenship,Purpose_Visit,Entry_Type,Visa_Category,Airline,Vessel_No,Date_Arrival,
Date_Departs,Collected_Date,Remarks) Values('" +txtPPT_No.Text+ "'," +application_Date.Value+ ",'" +txtRecieptNo.Text+ "','" +cmbcitizenship.Text+ "','"
+txtpurpose.Text+ "','" +cmbentry.Text+ "','" +cmbcategory.Text+ "','" +cmbAirLine.Text+ "','" +txtvesel.Text+
"'," +arrivalDate.Value+ ",'" +departsDate.Value+ ",'" +txtrefference_No.Text+ "'," +collected_Date.Value+ ",'" +txtremarks.Text+ "'";
all date fields i used datetimepicker control. where i went wrong?.
Error : "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
View 4 Replies
View Related
Aug 5, 2015
I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.
E.g.
Query 1
Select ID, Person, ProposedEvent, DayField, TimeField
from MyOptions
where person = 'me'
Table
Select Person, ExistingEvent, DayField, TimeField
from MyTimetable
where person ='me'
Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.
I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.
View 5 Replies
View Related
Jul 14, 2006
hello all,
i am making a query which select the data again a particuler date.
I insert values in the table for with current date(Today's date) and the records is inserted with the date format(2006-07-14 16:12:09),now when i run the query after 2 or 3 minutes to select the records inserted today, my query returns no results.
I think it is because of the the time (14:16 in this case) that after 2 minutes, the query looks for the records inserted at (2006-07-14 18:12 or 2006-07-14 19:12) and does not get the result.
Is there a method to not consider the time(14:16) when running the query but the query fetches the records including the records inserted at this time(14:16) no matter at what time I run the query today?
Please anyone help me!
Thanks in advance!
View 4 Replies
View Related
Jul 20, 2005
Hi all,I'm working on the schema for a database that must represent data about stock& bond funds over time. My connundrum is that, for any of several dimensionfields, including the fund name itself, the dimension may be represented indifferent ways over time, and may split or combine from one period to thenext.When querying from the database for an arbitrary time period, I need the datato be rolled up to the smallest extent possible so that apples can be comparedto apples. For instance, if the North America region becomes 2 regions, USAand Canada, and I query a time period that spans the period in which thissplit occurred, I should roll up USA and Canada and for records in the periodthat has both, and I should call the result something like "(NorthAmerica)(USA/Canada)" in the output. The client specifies that the dimensionoutput must represent all dimensions that went into the input.Of course, I have to account for more complex possibilities as well, e.g.Fund-A splits into Fund-B and Fund-C, then Fund-C merges into Fund-D producing(Fund-A/Fund-D)(Fund-B/Fund-C/Fund-D)(Fund-B/Fund-D)I can think of several ways to handle this issue, and they're allextraordinarily complex and ugly. Any suggestions?Thanks,- Steve Jorgensen
View 9 Replies
View Related
Dec 5, 2007
how do I convert DATEPART(year, GETDATE()) - 1 into a string so I can combine with
'10/1/' to form '10/1/2006'
select '10/1/' + DATEPART(year, GETDATE()) - 1 AS StartDate
View 1 Replies
View Related
Oct 18, 2015
I have 2 tables, i need to take the max date from PAY and Combine in MEN
MEN
======
id | Fname
========
1 | AAA
2 | BBB
3 | CCC
PAY
===
id | Tdate
=========
1 | 01.01.2015
1 | 02.01.2015
1 | 03.01.2015
2 | 06.01.2015
3 | 09.01.2015
3 | 10.01.2015i
I need to show this:
id | Fname | Tdate
=============
1 | AAA | 03.01.2015
2 | BBB | 06.01.2015
3 | CCC | 10.01.2015
View 5 Replies
View Related
Jul 15, 2014
All source and target date fields are defined as data type "smalldatetime". The "select" executes without error though when used with "insert into" it fails with the error:
Msg 295, Level 16, State 3, Line 25: Conversion failed when converting character string to small date-time data type..I am converting from a character string to smalldatetime since the source and target date columns are "smalldatetime". All other columns for the source and target are nvarchar(255). I assume there is an implicit conversion that I don't understand. In a test, I validated that all dates selected evaluate ISDATE() to 1.
USE [SCIR_DataMart_FromProd_06_20_2014]
GO
IF OBJECT_ID ('[SCIR_DataMart_FromProd_06_20_2014].[dbo].[IdentifierLookup]', 'U') IS NOT NULL
DROP TABLE [SCIR_DataMart_FromProd_06_20_2014].[dbo].[IdentifierLookup]
[code]....
View 9 Replies
View Related
Jan 30, 2007
I asked this question below, but the answer was that the conversion will take place automatically, but I can't get that to happen. I have a flat file with an 8 position field that I identify as string (and I also tried date) that is yyyymmdd and it needs to go into the database field that is datetime format. IS there something I am doing wrong with the definition of it, or do I need to add some kind of conversion, and if so, what and how would that be done. I'm a dts Sql2000 expert, but the SSIS thing is driving me crazy. I have a ton of dts' to convert and the migration tool doesn't work because there are a lot of active X scripts in them. thanks for your help. Boston Rose
View 1 Replies
View Related
Oct 22, 2015
I am trying to load previous days data at 3 am via a SSIS job.
The Date variable is initiated as DATEADD("dd",-1, GETDATE()) in the for loop.
Now, as this job runs at 3 am, and I set the variable as GETDATE() - 1, it excluded the data from 12 am to 3 am in the resultset as Date is set as YYYY-MM-DD 03:00:00:000 I need this to be set as YYYY-MM-DD 00:00:00:000
How can i do this?
View 2 Replies
View Related
Jun 13, 2002
In SQL Server 2000:
How do I convert a Julian date to a Gregorian date?
How do I convert a Gregorian date to Julian?
Examples please.
Many thanks in advance.
Gary Andrews
View 2 Replies
View Related
Mar 18, 2014
I have the following
Column Name : [Converted Date]
Data Type : varchar(50)
When I try and do month around the [Converted Date] I get the following error message
“Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.”
My Query is
SELECT
month([Created Date])
FROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting]
View 7 Replies
View Related
Nov 16, 2015
SELECT * ,[Due]
FROM [Events]
Where Due >= getdate() +90
This returns the error: Conversion failed when converting date and/or time from character string
Why would this be? How to cast or convert this so that it will work?
View 24 Replies
View Related
Sep 5, 2007
Dear Expert!
A server with SQL 2005 sp2, Reporting Services and Sharepoint services (ver 3.0) (in integrated mode) gives an odd error. When viewing a Reporting Services report with a Date Time Picker, the date chosen is wrong. The preferred setting is Danish with the date format dd-mm-yyyy. The date picker shows the months in Danish but when selecting a date, and clicking on the Apply-button, the date reformats to US (mm-dd-yyyy).
Example:
When choosing 5th of September 2007 and clicking apply, it shows in the picker, 9th of May 2007.
When choosing 26th of September 2007 and clicking apply, it shows, again in US format, the RIGHT date but adds a timestamp 12:00 AM? in the end, making further enquiries to fail.
The report itself receives the right date and shows correctly. The only case it fails is, when the time stamp appears.
The server is a 32-bit one with 4 GB RAM. A testserver with identical collation on the Reportserver database cannot recreate the error. The site containing the reports has been set to Danish in the regional settings. To Reinstall is not an option.
The test report has no database connection whatsoever.
When setting the site to US, the timestamp wont appear at all.
The server has been restarted and the installation procedure was of the simple kind. No special tweaks at all.
Any advice would be greatly appreciated.
Kind Regards
Johan Rastenberger
View 1 Replies
View Related
Jan 15, 2002
Hello,
we need to track date/time of last update for each record in a table.
As we understand it, we can't use field type Timestamp as this type does
not use dates/times.
Is there any SQL function available which we can bind to a column or
do we really have to use triggers?
Greetings from Mannheim, Germany
Ricardo
View 2 Replies
View Related
Jan 15, 2002
Hello,
we need to track date/time of last update for each record in a table.
As we understand it, we can't use field type Timestamp as this type does
not use dates/times.
Is there any SQL function available which we can bind to a column or
do we really have to use triggers?
Greetings from Mannheim, Germany
Ricardo
View 1 Replies
View Related
Nov 11, 2013
I want to update the time in a datetime field with the current time.
Fields current value is:
2013-11-11 00:00:00.000
I want to insert this into another table and when I do I want to grab the current time and update that field.
field name: picked_dt
Table: oeordlin
or is there a way through sql to update the time when the picked_dt is updated?
View 2 Replies
View Related
Jun 12, 2007
Hi
i have the following situation. in my database i have a datetime field (dd/mm/yy hh:mms) and i also have a field timezone.
the timezone field has values in minutes that i should add to my datetime field so i have the actual time.
afterwards i split the datetime into date and time.
the last part i can accomplish (CONVERT (varchar, datetime, 103) as DATEVALUE and CONVERT (varchar, DATETIME, 108) as TIMEVALUE).
could anybody tell me how i can add the timezone value (in minutes) to my datetime value ?
i do all the calculations in my datasource (sql).
Thanks
V.
View 3 Replies
View Related
Mar 16, 2014
I want to display only the date part of a date field which contains both date & time information.
For example I have the value '2013-11-14 00:00:00.000' in my result set, and ideally I would like to show only '2013-11-14'.
I have looked up the datepart() command, however I can't work out how to return all parts of the date, rather than just the year, month, or day.
View 3 Replies
View Related
Jul 4, 2014
I have passed createdDate from UI to Stored procedure.createdDate field declared with DateTime.it is having value 2014-07-01.I need to fetch records from the database based upon the created field.but Create_TM in database having value Date with timestamp.so how would i change the createdfield in stored procedure.
ALTER PROCEDURE [dbo].[ByDateRange]
@Feed VARCHAR(50),
@CreatedDate DATETIME
select * from Date_table where Create_TM = @CreatedDate
View 1 Replies
View Related
Dec 29, 2005
I have a table named "shift" and I need to setup my query to return only data where the field "startime" = today. The problem I am running into is the starttime field it laid out like "2005-12-29 14:00:00" with different time values. I need to ruturn everything that has todays date regardless of the time value. I tried using GetDate() but that is returning data for other days as well or just data before or after the current time. Does anyone have any suggestions? This is driving me crazy! Thanks, Garrett
View 7 Replies
View Related
Oct 4, 2007
Hello,
We have a bunch of Audit tables that contain almost exact copies of the operations tables. The audit tables also include:
AuditID - the audit action (insert, modify - old, modify - new, deleted)
AuditDate - date and time of action
AuditUser - User who did it...
At the end of the day I need to know for any given record what it looked like at the beginning of the day and what it looks like at the end of the day. There could have been numerous changes to the record throughout the day, those records I am not interested in. Only the first record and the last record of a give day.
I am going to be doing a lot of MIN(AuditDate) and MAX(AuditDATE) and .. WHERE AuditDate BETWEEN '10/1/2007 00:00:00' AND '10/1/2007 11:59:59' ...
Question: Whats better for performance:
1. Separating out the date and time and doing a clusterd index on the date.
2. Keeping date and time in the same column and just use a normal index.
3. Better ideas?
Thanks,
Bradley
View 1 Replies
View Related
Feb 25, 2008
A view named "Viw_Labour_Cost_By_Service_Order_No" has been created and can be run successfully on the server.
I want to import the data which draws from the view to a table using SQL Server Import and Export Wizard.
However, when I run the wizard on the server, it gives me the following error message and stop on the step Setting Source Connection
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Error)
Messages
Error 0xc020801c: Source - Viw_Labour_Cost_By_Service_Order_No [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0014019. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
(SQL Server Import and Export Wizard)
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
- Setting Destination Connection (Stopped)
- Validating (Stopped)
- Prepare for Execute (Stopped)
- Pre-execute (Stopped)
- Executing (Stopped)
- Copying to [NAV_CSG].[dbo].[Report_Labour_Cost_By_Service_Order_No] (Stopped)
- Post-execute (Stopped)
Does anyone encounter this problem before and know what is happening?
Thanks for kindly reply.
Best regards,
Calvin Lam
View 6 Replies
View Related
Apr 7, 2006
SELECT *
FROM dbo.empBenefits q
WHERE (StartDate IN
(SELECT TOP 2 STARTDATE
FROM EMPBENEFITS
WHERE EMPBENEFITS.employeeno = q.employeeno AND Benefitcode = 'HON'
ORDER BY startdate ASC))
I have this select statement working however I need to combine 2 records in a single row in a single table. The unique key is Employee No.
View 1 Replies
View Related
Apr 12, 2006
Hey guys,
I realize I've posted something like this before, but i'm confused how to do something new, what I am trying to do is combine rows of data when certain columns equal one another.
For example, I have to sets of data who have in common the columns ctp_code and a mod_code (they are the same) however they have two other columns called billing_amt and amount_owed that need to be added together (literally taking the data and doing the math, so 100.00 + 100.00 = 200.00)
Code:
id ctp_code mod_code billing_amt amount_owed
1 1 1 100.00 100.00
2 1 1 100.00 100.00
My results should be this:
Code:
id ctp_code mod_code billing_amt amount_owed
1 1 1 200.00 200.00
Any help, thoughts?
View 10 Replies
View Related
Jul 1, 2014
I have two tables that I need to combine, they are as follows:
AccountCheckInDT
3289442/14/2014 15:11
3289442/14/2014 15:21
3289442/15/2014 15:24
3289442/15/2014 17:45
3289442/15/2014 23:58
3289442/16/2014 20:09
3289442/17/2014 11:27
3289442/19/2014 9:35
3289442/19/2014 9:41
AccountCheckOutDT
3289442/14/2014 16:18
3289442/16/2014 0:27
3289442/19/2014 9:28
3289442/19/2014 10:00
This is how I need the end result to look:
AccountCheckInDT CheckOutDT
3289442/14/2014 15:11 2/14/2014 16:18
3289442/14/2014 15:21
3289442/15/2014 15:24
3289442/15/2014 17:45
3289442/15/2014 23:58 2/16/2014 0:27
3289442/16/2014 20:09
3289442/17/2014 11:27
3289442/19/2014 9:35 2/19/2014 9:28
3289442/19/2014 9:41 2/19/2014 10:0
View 3 Replies
View Related
