We help IT Professionals succeed at work.

HOW to reterive specific datetime values from sybase iq

2,892 Views
Last Modified: 2014-05-15
select * from TEMP_TABLE  
WHERE
DATE_CREATED <= dateformat(convert(datetime,'05/8/2014 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
Comment
Watch Question

Author

Commented:
Can anybody please give me correct syntax
select *
from
TEMP_TABLE  
where dateformat(convert(datetime,DATE_CREATED),'MM/DD/YYYY HH:MM AM')='5/8/2014 11:23 AM'

Result set is zero rows,
it should give 533 records instead.
alpmoonSybase DBA
CERTIFIED EXPERT

Commented:
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 )

Author

Commented:
can you please give one example for my query,
select *
from
TEMP_TABLE  
where dateformat(convert(datetime,DATE_CREATED),'MM/DD/YYYY HH:MM AM')='5/8/2014 11:23 AM'


Thanks,
Sybase DBA
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.