Avatar of welcome 123
welcome 123
 asked on

HOW to reterive specific datetime values from sybase iq

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
Sybase DatabaseDB2Microsoft SQL Server

Avatar of undefined
Last Comment
alpmoon

8/22/2022 - Mon
welcome 123

ASKER
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.
alpmoon

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 )
welcome 123

ASKER
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,
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
alpmoon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question