welcome 123
asked on
HOW to reterive specific datetime values from sybase iq
select * from TEMP_TABLE
WHERE
DATE_CREATED <= dateformat(convert(datetim e,'05/8/20 14 11:23 AM'),'MM/DD/YYYY HH:MM AM')
> Script lines: 1-3 --------------------------
SQL Anywhere Error -1001014: Data conversion failed
-- (dfe_HDBValue.cxx 373)
Msg: 21, Level: 14, State: 0
Line: 0
I am trying this syntax but this syntax is not working, can anybody help on this, this is kind of urgent for me.
Thanks & Regards,
Sailaja
WHERE
DATE_CREATED <= dateformat(convert(datetim
> Script lines: 1-3 --------------------------
SQL Anywhere Error -1001014: Data conversion failed
-- (dfe_HDBValue.cxx 373)
Msg: 21, Level: 14, State: 0
Line: 0
I am trying this syntax but this syntax is not working, can anybody help on this, this is kind of urgent for me.
Thanks & Regards,
Sailaja
You need either to choose one of the styles below or to use convert function twice (one for date and another one for time and concatenate them).
CONVERT( datatype, expression [ , format-style ] )
datatype The data type to which the expression is converted.
expression The expression to be converted.
format-style The style code to apply to the output value. Use this parameter when converting strings to date or time data types, and vice versa. The table below shows the supported style codes, followed by a representation of the output format produced by that style code. The style codes are separated into two columns, depending on whether the century is included in the output format (for example, 06 versus 2006).
Without century (yy) style codes With century (yyyy) style codes Output format
- 0 or 100 Mmm dd yyyy hh:nnAA
1 101 mm/dd/yy[yy]
2 102 [yy]yy.mm.dd
3 103 dd/mm/yy[yy]
4 104 dd.mm.yy[yy]
5 105 dd-mm-yy[yy]
6 106 dd Mmm yy[yy]
7 107 Mmm dd, yy[yy]
8 108 hh:nn:ss
- 9 or 109 Mmm dd yyyy hh:nn:ss:sssAA
10 110 mm-dd-yy[yy]
11 111 [yy]yy/mm/dd
12 112 [yy]yymmdd
- 13 or 113 dd Mmm yyyy hh:nn:ss:sss (24 hour clock, Europe default + milliseconds, 4-digit year )
- 14 or 114 hh:nn:ss:sss (24 hour clock)
- 20 or 120 yyyy-mm-dd hh:nn:ss (24-hour clock, ODBC canonical, 4-digit year)
- 21 or 121 yyyy-mm-dd hh:nn:ss.sss (24 hour clock, ODBC canonical with milliseconds, 4-digit year )
CONVERT( datatype, expression [ , format-style ] )
datatype The data type to which the expression is converted.
expression The expression to be converted.
format-style The style code to apply to the output value. Use this parameter when converting strings to date or time data types, and vice versa. The table below shows the supported style codes, followed by a representation of the output format produced by that style code. The style codes are separated into two columns, depending on whether the century is included in the output format (for example, 06 versus 2006).
Without century (yy) style codes With century (yyyy) style codes Output format
- 0 or 100 Mmm dd yyyy hh:nnAA
1 101 mm/dd/yy[yy]
2 102 [yy]yy.mm.dd
3 103 dd/mm/yy[yy]
4 104 dd.mm.yy[yy]
5 105 dd-mm-yy[yy]
6 106 dd Mmm yy[yy]
7 107 Mmm dd, yy[yy]
8 108 hh:nn:ss
- 9 or 109 Mmm dd yyyy hh:nn:ss:sssAA
10 110 mm-dd-yy[yy]
11 111 [yy]yy/mm/dd
12 112 [yy]yymmdd
- 13 or 113 dd Mmm yyyy hh:nn:ss:sss (24 hour clock, Europe default + milliseconds, 4-digit year )
- 14 or 114 hh:nn:ss:sss (24 hour clock)
- 20 or 120 yyyy-mm-dd hh:nn:ss (24-hour clock, ODBC canonical, 4-digit year)
- 21 or 121 yyyy-mm-dd hh:nn:ss.sss (24 hour clock, ODBC canonical with milliseconds, 4-digit year )
ASKER
can you please give one example for my query,
select *
from
TEMP_TABLE
where dateformat(convert(datetim e,DATE_CRE ATED),'MM/ DD/YYYY HH:MM AM')='5/8/2014 11:23 AM'
Thanks,
select *
from
TEMP_TABLE
where dateformat(convert(datetim
Thanks,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
select *
from
TEMP_TABLE
where dateformat(convert(datetim
Result set is zero rows,
it should give 533 records instead.