on 06-30-2008 2:08 PM
Hi All,
I need to insert timestamp in milliseconds into the SQL database. The database is taking date but when i'm trying to insert along with the time, it is throwing the following error "Incorrect syntax near 12".
I have given the datatype as DateTime in XI.
Please let me know how i can solve this issue.
Regards,
Manisha Gogineni
Does SQL takes date time only in one specific format or we can give any format to insert date time into SQL from XI?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Manisha, Pushpinder,
Try using convert function istead of TODATE.
CONVERT ( data_type , expression , style )
Style ID Style Type
0 or 100 mon dd yyyy hh:miAM (or PM)
101 mm/dd/yy
102 yy.mm.dd
103 dd/mm/yy
104 dd.mm.yy
105 dd-mm-yy
106 dd mon yy
107 Mon dd, yy
108 hh:mm:ss
9 or 109 mon dd yyyy hh:mi:ss:mmmAM (or PM)
110 mm-dd-yy
111 yy/mm/dd
112 yymmdd
13 or 113 dd mon yyyy hh:mm:ss:mmm(24h)
114 hh:mi:ss:mmm(24h)
20 or 120 yyyy-mm-dd hh:mi:ss(24h)
21 or 121 yyyy-mm-dd hh:mi:ss.mmm(24h)
126 yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
130 dd mon yyyy hh:mi:ss:mmmAM
131 dd/mm/yy hh:mi:ss:mmmAM
the first 3 digits are to be used as style in the syntax for the format following it.
or try using the following UDF, provided incoming date format is same as the style id mentioned.
-
if(Date!="")
{
return "CONVERT(varchar,"Date",126)";
}
else
return Date;
-
Hi,
I have given CONVERT(datetime,'2008-15-01 08:19:36.688',121) and the datatype in XI as well as database is DATETIME, but it is giving an error saying "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
Regards,
Manisha Gogineni
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi All,
I have tried using the convert function. The statement we are trying to give to the SQL database is CONVERT('2008-15-01 08:19:36.688','YYYY-MM-DD HH24:MI:SS.FF',121). its still showing the following error Incorrect syntax near '2008-19-01 08:19:36.688'.
Regards
Manisha
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi ALL,
The blog speaks about inserting into an oracle database. Does the same holds true for an SQL database also?
Please help!!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Dear Manisha,
See this Note from SAP:
Date Field Handling
o Q: I would like to insert a date field into a table using the JDBCReceiver Adapter. However, I always get an error about a data type conversion error. What is the procedure to insert a date field using a JDBC Receiver?
o A: The actual implementation depends on the DBMS you are using.This answer will first describe a generic approach to implement this functionality and then provide examples for popular DBMS. All DBMS offer some kind of functionality to convert one data type into another data type. As the XML document used by the JDBC Receiver is based on character strings, we need to use a conversion function to convert a string into a DBMS-specific date type. This conversion function will be embedded by the JDBC Receiver into the SQL statement sent to the DBMS. In order that the DBMS actually
executes the conversion function and does not treat it as a string, we need to make sure that the JDBC Adapter does not quote the date parameter. This can be achieved by setting the hasQuot attribute of the respective date field's XML element to "No".
An example for the Oracle DBMS, where the date conversion function is named TO_DATE:
<DateField hasQuot="No">TO_DATE('2004-07-20 08:00:00', 'yyyy-mm-dd hh:mi:ss')</DateField>
As you can see, any occurrence of an apostrophe within the element data needs to be written as "'" in order to yield valid XML.
For the Microsoft SQL Server DBMS, the statement looks as follows:
<DateField hasQuot="No">CONVERT(DATETIME, '2005-01-01 01:23:45', 120)</DateField>
The third parameter specifies the date format expected by the
CONVERT function. For details, refer to the MS SQL Server
documentation (Books Online -> Contents -> Transact-SQL Reference
-> CAST and CONVERT).
Hope with this you can solve time stamp as well .
Best Regards
Praveen K
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Use DateTrans function to translate the source date to target date in the required format. also check in the SQL that column is in DateTime data type.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
>The database is taking date but when i'm trying to insert along with the time,
See that in the Database you have declared the Variable to accomodate the Data & Time Stamp.
Regards
Agasthrui Dosss
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
This blog will help you
/people/alessandro.berta/blog/2005/10/04/datetime-datatypes-and-oracle-database
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.