How to insert Time Stamp for SQL Database

[Former Member]

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

[Former Member · Accepted solution]

Does SQL takes date time only in one specific format or we can give any format to insert date time into SQL from XI?

[Former Member · Accepted solution]

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;

-

---