Help, Weird Int To Date Data Conversion
Dec 5, 2007
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?
Thanks
CdnGator (Jason)
View 4 Replies
ADVERTISEMENT
Jan 15, 2008
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)
select @html = '<html>' + getDate() + '</br>' + eventData().value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(100)')
+ '</br>' + ERROR_MESSAGE() + '</html>'
PRINT 'Warning: Unable to submit change to audit'
SELECT ERROR_MESSAGE()
exec util_EmailOut_DatabaseMail_prc @from = '<address>',
@to = '<address>',
@cc = null,
@bcc = null,
@subject = 'Change Tracking Insert Failure',
@body = null,
@HTMLBody = @html,
@importance = 1,
@file = null
END
END CATCH
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.
View 6 Replies
View Related
Jan 31, 2007
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
Thanks
View 14 Replies
View Related
Jun 22, 2006
Dear friends,
I am struggling to insert a date value in "dd-mm-yyyy" format to SQL server table having datatype as Date/Time.
Regional date setting for Server and Local systems are in dd-mm-yyyy format.
I am using following code
Dim DT As String
DT = Now()
DT = Format(DT, "MM/DD/YYYY")
for the inserting into table using command
ins_comm.CommandText = " INSERT INTO CARD (doe) values("& DT &")"
Procedure will end without any error ,but stored value for the date feild will be garbage value like 1900-01-01.
But if i used mm-dd-yyyy format ,it will get inserted.
What could be the reason,How I can save value in dd-mm-yyyy or dd-mm-yy format
Please help!!!
Graceson Mathew
View 1 Replies
View Related
Nov 30, 2015
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?
View 10 Replies
View Related
Sep 2, 2015
I am using T-SQL I have a column (ColA)that has datetime format and I simply want to pull the next day but date only into ColB
ColA
3/12/2014 12:00AM
3/19/2014 12:00AM
ColB
3/13/2014
3/20/2014
I have been trying the command below but keep getting the error "Conversion failed when convertint the varchar value '03-03-2014' to data type int."
Convert (varchar(10), "StartDate", 110)+1 as Next Day
View 8 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
Jun 15, 2004
Hi all,
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)
View 8 Replies
View Related
Jul 20, 2005
SQL Server 7.0The following SQL:SELECT TOP 100 PERCENT fldTSRID, fldDateEnteredFROM tblTSRs WITH (NOLOCK)WHERE ((fldDateEntered >= CONVERT(DATETIME, '2003-11-21 00:00:00',102))AND(fldDateEntered <= CONVERT(DATETIME, '2003-11-23 23:59:59', 102)))returns this record:fldTSRID: 4fldDateEntered: 24/11/2003Hello? How is 24/11/2003 <= '2003-11-23 23:59:59'?I tried decrementing the second predicate by seconds:(fldDateEntered <= CONVERT(DATETIME, '2003-11-23 23:59:30', 102)))returns the record, but(fldDateEntered <= CONVERT(DATETIME, '2003-11-23 23:59:29', 102)))does NOT.What is happening here?Edward============================TABLE DEFINITION:if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblTSRNotes_tblTSRs]') and OBJECTPROPERTY(id,N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblTSRNotes] DROP CONSTRAINT FK_tblTSRNotes_tblTSRsGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblTSRs]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[tblTSRs]GOCREATE TABLE [dbo].[tblTSRs] ([fldTSRID] [int] IDENTITY (1, 1) NOT NULL ,[fldDealerID] [int] NOT NULL ,[fldWorkshopGroupID] [int] NULL ,[fldSubjectID] [int] NULL ,[fldReasonID] [int] NULL ,[fldFaultID] [int] NULL ,[fldContactID] [int] NULL ,[fldMileage] [int] NULL ,[fldFirstFailure] [smalldatetime] NULL ,[fldNumberOfFailures] [int] NULL ,[fldTSRPriorityID] [int] NULL ,[fldTSRStatusID] [int] NULL ,[fldAttachedFilePath] [char] (255) NULL ,[fldFileAttached] [smallint] NOT NULL ,[fldFaultDescription] [ntext] NULL ,[fldFaultRectification] [ntext] NULL ,[fldEmergency] [int] NOT NULL ,[fldDateEntered] [smalldatetime] NOT NULL ,[fldEnteredBy] [int] NOT NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO
View 8 Replies
View Related
Jul 20, 2005
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
View 4 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
Mar 12, 2008
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) Derived Column Transformation Editor
2.1) Assign Derived Column Name, e.g.
INTERCHANGE_NET_APP_DATE
2.2) Select <add as new column> within Derived Column.
2.3) Enter the conversion Expression, e.g. ; -
2.3.1)
(SUBSTRING(INTERCHANGE_NET_APP_DATE_SRC,8,2) + "/" + SUBSTRING(INTERCHANGE_NET_APP_DATE_SRC,5,2) + "/" + SUBSTRING(INTERCHANGE_NET_APP_DATE_SRC,1,4))
2.3.2)
Since the above conversion is such a common task I suggest that Service Pack 3 of SQL Server 2005 delivers the following functionality; -
STRINGTODATE ('YYYYMMDD',INTERCHANGE_NET_APP_DATE_SRC)
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.
Thanks in advance,
Kieran.
View 1 Replies
View Related
Feb 7, 2005
I have a query that is behaving a little a weird. here is the example:
i have 1 table in this table i have 2 columns wich are date and time
DATE_DEBUT_PERIODE_FISCALE DATE_FIN_PERIODE_FISCALE
------------------------------ ---------------------------
1/27/1997 2/27/1997
1/1/2005 2/6/2005
here is my query:
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. ?
View 7 Replies
View Related
Mar 27, 2006
Hi guys,
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.
Thanks in advance.
Grey
View 1 Replies
View Related
Jan 7, 2008
Hi Experts,
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.
Can anyone suggest how to solve this issue.
View 11 Replies
View Related
Jan 22, 2008
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 }
SP1 CREATE PROCEDURE ws_Admin_Announcements_AddlAnnouncement
2
3 @subgroupid varchar(12),
4 @title nvarchar (100),
5 @announcement ntext,
6 @expiration smalldatetime,
7 @who varchar(12)
8
9 AS
10
11 insert into tblannouncements (fk_strsubgroupid,strtitle,strannouncement,dteexpires,dtecreated)
12 values (@subgroupid,@title,@announcement,@expiration,getdate())
13
Table Layout
fk_strSubGroupID varchar 12strTitle nvarchar 200strAnnouncement ntext 16dteExpires smalldatetime 4dteCreated smalldatetime 4
Any help would be greatly appreciated. Thanks ^_^
View 17 Replies
View Related
Feb 14, 2008
My SQL script: -
CREATE TABLE #temp_1
(LEGAL_ENTITY varchar(3) ,
DESCRIPTION varchar(25)
)
DECLARE
@select varchar(2000),
@from varchar(2000),
@where varchar(4000),
@final varchar(8000)
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?
Please advice, Thank you.
View 10 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
Aug 6, 2005
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 ......
View 7 Replies
View Related
Sep 21, 2005
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
View 5 Replies
View Related
Feb 17, 2006
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.
View 2 Replies
View Related
Mar 19, 2001
Hello All,
I need help in converting a date. What i'm looking for is date in format of mm/yyyy.
Thanks in advance.
View 1 Replies
View Related
Nov 27, 2001
I need to import a text file into a table by using DTS.
How to convert a text date to smalldate type ?
Thanks.
View 1 Replies
View Related
Aug 11, 2003
Hi all
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
TIA
View 2 Replies
View Related
Oct 29, 2003
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?
thanks
View 3 Replies
View Related
Mar 6, 2002
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
View 2 Replies
View Related
Jan 19, 2004
Is use this stored procedure.
This is the error mesage: "Syntax error converting datetime from character string"
Please help me !
Alter Procedure "Selectie_Date_Tabel" (@datainceput datetime, @datasfirsit datetime,@Grupa AS nvarchar(20))
As
set nocount on
DECLARE @NEWLINE AS char(1)
SET @NEWLINE = CHAR(10)
DECLARE @keyssql AS varchar(1000)
SET @keyssql = 'SELECT * FROM View2'
+ @NEWLINE + 'WHERE [Cod grupa] = ' + CHAR(39) + @Grupa + CHAR(39)
+ @NEWLINE + 'AND ([Day] BETWEEN ' + CONVERT(DATETIME, @datainceput , 120) + ' AND ' + CONVERT(DATETIME, @datasfirsit , 120) +')'
EXEC (@keyssql)
View 12 Replies
View Related
Jan 28, 2004
Hi,
I have my dates in DB2 source in two formats -
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.
Any help is appreciated.
Thanks,
Vivek
View 7 Replies
View Related
Apr 25, 2008
Hi am trying to convert my date from the date times stap to
this format 250408
I've tried this : select convert(varchar,getdate(),112)
but the result am getting is '20080425'
View 3 Replies
View Related
Jun 15, 2006
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.
why it shows an error"
View 1 Replies
View Related
Feb 6, 2008
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.
View 1 Replies
View Related
Apr 4, 2006
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.
input data example - 2006-03-07-14.42.34
Any ideas? .
View 6 Replies
View Related
