I need to convert an integer value fo for example 1071123 to a date field. This value would represent 107 = Year 2007, 11 = Month November, 23 = 23rd day of month. So effectively this would translate to 2007-11-23 00:00.000.
I would like to do this in the Integration Services package. I am retrieving data from an AS/400 system to an SQL Server DB. I'm not sure if I can do this with the Derived Column object or is there a better way to achieve this conversion.
Please, can anyone shed some light on this for me?
So what I'm trying to do is audit changes on a server. I'm creating a DDL trigger as below:
Code Block
CREATE trigger DDL_changeTracking_tr on Database FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE, CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION, CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER, CREATE_VIEW, ALTER_VIEW, DROP_VIEW as SET NOCOUNT ON SET ANSI_WARNINGS ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ARITHABORT ON SET CONCAT_NULL_YIELDS_NULL ON SET NUMERIC_ROUNDABORT OFF SET QUOTED_IDENTIFIER ON
BEGIN TRY BEGIN declare @login varchar(100) set @login = eventData().value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(100)')
if (@login <> 'sqladmin' and @login <> 'sqlagentadmin') BEGIN insert into DBMonitoring..audit_tbl (databaseId, auditTime, loginName, objectName, objectType, eventType) select DB_ID() as databaseId , getDate() as auditTime , eventData().value('(/EVENT_INSTANCE/SchemaName)[1]', 'varchar(100)') + '.' + eventData().value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(100)') as objectName , eventData().value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(100)') as objectType , eventData().value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(100)') as LoginName , eventData().value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)') as eventType END
END END TRY BEGIN CATCH BEGIN declare @html varchar(max)
GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ENABLE TRIGGER DDL_changeTracking_tr ON DATABASE
It inserts the trigger data into:
Code Block
CREATE TABLE audit_tbl ( databaseId int not null, --auditTime datetime default getDate() not null, auditTime datetime not null, loginName varchar(255) not null, objectName varchar(255) not null, objectType varchar(25) not null, eventType varchar(40) not null ) go ALTER TABLE audit_tbl ADD CONSTRAINT PK_audit_tbl_databaseId_auditTime_objectName PRIMARY KEY (databaseId, objectName, auditTime) CREATE NONCLUSTERED INDEX IX_audit_tbl_auditTime_loginName ON audit_tbl(auditTime, loginName) CREATE NONCLUSTERED INDEX IX_audit_tbl_auditTime_objectType ON audit_tbl(auditTime, objectType)
In the same database that I've run this one, I'm running this code to test it:
Code Block
create procedure cow_prc as select 1 go drop procedure cow_prc Occassionally when I run this, I get the following error: Msg 241, Level 16, State 1, Procedure DDL_changeTracking_tr, Line 42 Conversion failed when converting datetime from character string.
I am completely lost on this. I've had 3 fellow DBAs look at it and they're not sure what's going on with it. I've even tried writing the trigger logic as a CTE which using isDate() to make sure that auditTime actually is a date.
Any insight would be greatly appreciated. Thanks in advance.
I have some data which I am trying to put into a DM where I can use it as part of a cube (my first!!)
I have hit a small problem with dates, I get it from the ERP system as a numeric field, and I need to convert it to a date format. The intension is to use this converted data with Named Calculations to derive Year, month Day ect.
However I cannot seem to be able to convert and store (in SQL) this column can anyone advise
I am trying to upload data from CSV to Sql table. I have a column as 'arrived_date' value '13:45' etc and while trying to load data i am getting error as "data conversion failed ,truncation may occur while loading data". In flat file connection this column datatype is string but in my table datatype is as time(). There is a error with conversion. I tried to change data type in advanced editor but no use. Using data conversion after flatfile makes my error disappear but it is giving error right at the file not even going through from file?
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]
i 've got a real strange problem. I 'v got an asp.net/vb.net application and a mssql db at the end. I have a form where I can insert some info in the tables. Everything went well for a couple of months, but now my dates transform on a real strange way. no matter wich date I give up, those date's never reach the db. example: date = 31-12-2004(Europe date) --> in the db the date is 21-07-1894 and I've seen the other dates, the month and the year are the same, they all show me july 1894.
Has everyone ever seen this before.
PS. I'm using win2000 service pack 4 with mssql 2000 (no service pack)
I'm having a fit with a query for a range of dates. The dates arebeing returned from a view. The table/field that they are beingselected from stores them as varchar and that same field also storesother fields from our dynamic forms. The field is called'FormItemAnswer' and stores text, integer, date, float, etc. Anythingthe user can type into one of our web forms. The query looks like,select distinct [Lease End Date] fromvwFormItem_4_ExpirationDateOfTerm where CONVERT(datetime, [Lease EndDate], 101) >= CONVERT(datetime, '08/03/2003', 101) ANDCONVERT(datetime, [Lease End Date], 101) < CONVERT(datetime,'09/03/2003', 101)The underlying view does a simple select based on the particular formfield, lease end date in this case.This query works fine with 1 date in the where but with two fails withthe dreaded 'syntax error converting to datetime from varchar'.What appears to be happening is sql is trying to do the CONVERTSbefore it filters with the WHERE clause in the view.I tried using a subquery but it still seems to do the same thingsomehow!SELECT *FROM (SELECT *FROM vwFormItem_4_McD_Lease_4B_ExpirationDateOfTermWHERE isdate([Lease End Date]) = 1 ) derivedWHERE (CONVERT(datetime, [Lease End Date], 101) >= CONVERT(datetime, '#8/3/2003', 101)) AND (CONVERT(datetime, [Lease End Date],101)<= CONVERT(datetime, '9/3/2003', 101))I've tried everything I know to try like doing the CONVERT inside theview I'm selecting from, doing a datediff, everything. Really goincrazy here.Any ideas would be greatly appreciated!Russell
Hi, Basically the above is a very common requirement, please comment on my solution which I've arrived at by searching through the web; -
In summary I have used 3 SSIS components these are "Flat File Source", "Derived Column" and "SQL Server Destination".
1) File Connections Manager Editor 1.1) Within File Connections Manager Editor; - Name the data type e.g. "INTERCHANGE_NET_APP_DATE_SRC" and assign a type to the data type e.g. string[DT_STR]
1.2) Click on the Preview button to ensure the expected text is assigned to the expected data type.
2.4) Select "database timestamp [DT_DBTIMESTAMP] " as Data Type.
2.5) Within the Mappings tab of the SQL Destination Editor have; - Input Column as INTERCHANGE_NET_APP_DATE and Destination Column as INTERCHANGE_NET_APP_DATE.
Please comment on the above, I will then pass on my suggestion to Microsoft.
BEGIN declare @datefin_flag datetime, @strip datetime SELECT @strip = dateadd(d,datediff(d,0,getdate()),0) SELECT @datefin_flag = DATE_FIN_PERIODE_FISCALE FROM DM_LKP_CALENDRIER_PERIODE_F WHERE DATE_DEBUT_PERIODE_FISCALE < @strip AND DATE_FIN_PERIODE_FISCALE = @strip --select @datefin_flag --select @strip IF(@datefin_flag != @strip) RAISERROR('You cant run this',16,1) END
Well this Query should return the raiserror it returns completes successfuly since todays date is not the same as the date in the database. if you select @datefin_flag it returns NULL and if you select @strip it brings back todays date how can NULL be equal to to todays date assuming that todays date is equal to NULL. ?
We've had Reporting Services running in a production environ. for 6 months fine, but from Saturday every report now causes the following error (in both the Report Manager and Soap calls):
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
Specified argument was out of the range of valid values. Parameter name: date
Now, before you jump to conclusions - this error is occurring on reports with both parameters and no parameters (ie in reports that have no "date" parameter in the report).
The next bit of info is the weird bit...
It was working on Friday (25/March/2006) - so as a test, i switched the servers clock back to Friday - and BINGO... it worked. Then I changed it to Saturday (26th March) and it doesnt work. In fact for the next 7 days - the service will not work until April 2nd 2006 - (when I changed the systems date to the 2nd it worked again.) Moving forward, it looks like its working fine.
Does anyone have any suggestions? This is in a production environment, so obviously changing the sytsem date as a quick fix workaround wont suffice.
I am extracting data from SQL Server 2005 to flat file destination. I am using SQL Command to specify the data selection query. One of my query uses Replicate function to derive a column value. When I execute this package it fails with the error "Data conversion failed. The data conversion for column "value" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page".
The reason for the problem is that, it is taking the InputColumnWidth of the flat file destination as 8000 and I specified the OutputColumnWidth as 4.
If I change the OutputColumnWidth to 8000, it is working without any error but resulting in the column width of 8000.
I tried using DerivedColumn Transformation's Type cast and DataConversion Transformation but still I am getting the same error in the respective Transformation components.
I know what it means. The problem is that I can't see where it's happening. Looking at SQL Profiler shows the info correct. Front end code only allows max of 50 characters for the title, 2000 for the announcement. Expiration date is picked from a date picker, subgroupID and who are both 12 characters and are session info (this is correct as it's used else where and they are working). This is from SQL Profiler: exec ws_Admin_Announcements_AddlAnnouncement @subgroupid = 'D4F4CB571A09', @title = N'over 12 characters', @announcement = N'<p>test</p>', @expiration = 'Jan 22 2008 11:41AM', @who = 'D05B47F2CFB1' Class:1 public int insertNewAnnouncement(string subgroupid, string title, string announcement, DateTime expiration,string who, string connectionString) 2 { 3 try 4 { 5 dbConnection = new SqlConnection(connectionString); 6 dbCommand = new SqlCommand("ws_Admin_Announcements_AddlAnnouncement", dbConnection); 7 dbCommand.CommandType = CommandType.StoredProcedure; 8 dbCommand.Parameters.Add("@subgroupid", SqlDbType.VarChar, 12); 9 dbCommand.Parameters[0].Value = subgroupid; 10 dbCommand.Parameters.Add("@title", SqlDbType.NVarChar, 100); 11 dbCommand.Parameters[1].Value = title; 12 dbCommand.Parameters.Add("@announcement", SqlDbType.NText); 13 dbCommand.Parameters[2].Value = announcement; 14 dbCommand.Parameters.Add("@expiration", SqlDbType.SmallDateTime); 15 dbCommand.Parameters[3].Value = expiration; 16 dbCommand.Parameters.Add("@who", SqlDbType.VarChar, 12); 17 dbCommand.Parameters[4].Value = who; 18 19 dbConnection.Open(); 20 intRowsAffected = dbCommand.ExecuteNonQuery(); 21 } 22 finally 23 { 24 dbCommand.Dispose(); 25 dbConnection.Dispose(); 26 } 27 28 return intRowsAffected; 29 }
SELECT @select = 'SELECT LEGAL_ENTITY, DESCRIPTION' SELECT @from = ' FROM table_1 WITH (NOLOCK)' SELECT @final = @select+@from
INSERT INTO #temp_1
( LEGAL_ENTITY, DESCRIPTION
) EXEC (@final)
SELECT LEGAL_ENTITY, DESCRIPTION From #temp_1
drop table #temp_1
By using above scrript, I will get a error message in SQL2005 database:- Server: Msg 8152, Level 16, State 14, Line 1 String or binary data would be truncated.
I will not error in SQL2000 or I comment exec(@final) and directly put in the SELECT statement, th
There is some SELECT statement modification in between that force me must use the variables to construct the SELECT statement. So can anyone advice, how to avoid the error?
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.
i do have date problem in sql server, i m using DD/MM/YYYY date format, & passing it to insert & update stat...& compairing it with data in table, which is not working properly, how to convert dd/mm/yyyy to mm/dd/yyyy or yyyy-mm-dd hoping for solution soon, thanx murli ......
I'm searching on a smalldatetime field in SQL Server so a typical value would be 09/21/2005 11:30:00 AM. I have a search form which offers the user a textbox to search by date and unless they enter the exact date and time, no matching records are found. Of course I want I all records for a given day to be returned. This is how I'm doing it now. Thanks. Dim dteDate_Requested As String = txtDate_Requested.Text If dteDate_Requested <> "" Then strSqlText += " Date_Requested='" & dteDate_Requested & "'"End If
HI everyne, I have a varchar field in one table, which contains data in the form '010706' and I want to convert this to date datatype to 01/07/2006 (Jan 07, 2006). When I just import the data to the other table it gets converted to 7/6/2001, how can I convert it right? Please help.
I wonder whether any of you can help me with a bit of code that you may have already had to execute??
I have a SQL database logging activities and a load of information in a mdb file that needs to be imported.
Unfortunately the data in the SQL database is in the format yyyy-mm-dd and the data in the mdb file is in dd/mmmm/yyyy.
When i run a DTS to import the data the new rows are imported as they were YYYY-dd-mm.:mad:
example: data logging as 2003-08-10 2003-08-11
imported data from last week arrives as 2003-01-08 2003-02-08 2003-03-08 etc
how can i manipulate the data in SQL to reverse the day and month numbers for Aug 1st to Aug 8th??
I have tried changing the mdb data format but that doesnt make a difference. I dont understand DTS enough to know whether it is possible there :confused: and my SQL skills dont rise to the challenge - yet!! :o
I have a datetime field in a table and I have to insert this datatime data into antoher table. In my insert statement I convert the datetime field into varchar and then insert it into the second table.
The date field in the original table is : 2/2002/13 3:58:12 PM but in the destination table i get: 2/2002/13 3:58:00 PM
I lose the seconds in the conversion, i think
Whats the best way to preserve to the datetime field during transfer?
I have one column that is a datetime, and another that is an INT which represents seconds. i cannot figure out how to subtract the seconds from the datetime column. sorry, i'm still kind of new to this TSQL. I get this error:Server: Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type datetime.
when i try to do this select dateColumn - IntColumn from Table
so i think there must be a way to make sql know that IntColumn is actually seconds. thanks
Format 1 - char(5) - Example - 10305. 1 indicates century,03 indicates year and 05 indicates month. The day is not stored. So this is 2003,May 1
Format 2 - char(7) - Example - 1030525. 1 indicates century,03 indicates year, 05 indicates month,25 indicates day. 2003,May 25
I want to convert the above two formats to SQL Server smalldatetime and I only need the DATEPART. The date needs to be in the format mm/dd/yyyy. The default day would be 01 when the day is not specified.
If the format is 00305 then the 0 indicated 19th century. So this is 1903, May 1.
priya writes "select convert(smalldatetime,'09/06/2006') is working
select convert(smalldatetime,'13/06/2006') it shows an error The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
I have a SQL database where the dates appear in the format "733433". If you convert in the "Select" statement, it's fine. You can use the day, month, year concatenated in an excel expression, converts fine. As an expression in reporting services, I receive an error. An help is appreciated.
I need help with date conversion from character data. In SQL 2000 we used a Date Time Conversion task
I do not see how to do this in SQL 2005 SSIS. I tried a data conversion task to a database timestamp and this is what I got:
[Data Conversion [383]] Error: Data conversion failed while converting column "date_time_stamp" (47) to column "Copy of date_time_stamp" (396). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Here is a sample of the input data I'm trying to convert.
