Remove time(date)

Question

Post reply

Remove time(date)

jcachado

Mr or Mrs. 500

Points: 514
More actions
November 27, 2015 at 1:04 am

#322555

Hi all,
Help me please,
How i can get only the date form this statment:
select distinct po_id po_id, dc_id,whse_id wh,trunc(create_dtim) data from irctl where (extr_flag is null or extr_flag ='N') and create_dtim < today and rct_qty > 0 order by trunc(create_dtim)
For now return this value:
16-11-2015 00:00:00
Already try the to_char(create_dtim, "%d %B %Y") but have a error i my Visual Studio 🙁 (Does not allow the character%)
Thks a lot,
Cachado

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 1

Is your query a T-SQL statement?

Igor Micev,My blog: www.igormicev.com

BrainDonor SSCoach Points: 19252 More actions · Answer 2

SELECT FORMAT(GETDATE(),'dd-MM-yyyy') is one way to do it - there are others.

I'd remove the formatting of the date from the ORDER BY - just ordering by the unformatted date would do the task just as well.

Steve Hall
Linkedin
Blog Site

jcachado Mr or Mrs. 500 Points: 514 More actions · Answer 3

Hi,

Don´t work 🙁 with this FORMAT(GETDATE(create_dtim),'dd-MM-yyyy') !!

ERROR [HY000] [INTERSOLV][ODBC Informix driver][Informix]Procedure (getdate) not found.

Regards,

Cachado

BrainDonor SSCoach Points: 19252 More actions · Answer 4

That was just an example, using a SQL Server date function.

What does FORMAT(create_dtim,'dd-MM-yyyy')do?

Steve Hall
Linkedin
Blog Site

jcachado Mr or Mrs. 500 Points: 514 More actions · Answer 5

BrainDonor (11/27/2015)
That was just an example, using a SQL Server date function.
What does FORMAT(create_dtim,'dd-MM-yyyy')do?

no 🙁

ERROR [HY000] [INTERSOLV][ODBC Informix driver][Informix]Procedure (format) not found.

Regards,

Cachado

Ant-Green SSC Guru Points: 113445 More actions · Answer 6

You may be best posting this on an IBM Informix forum instead of a Microsoft SQL Server forum.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 7

BrainDonor (11/27/2015)
That was just an example, using a SQL Server date function.
What does FORMAT(create_dtim,'dd-MM-yyyy')do?

Just so you know, FORMAT is 44 times slower than either CAST or CONVERT.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcachado Mr or Mrs. 500 Points: 514 More actions · Answer 8

Jeff Moden (11/27/2015)
BrainDonor (11/27/2015)
That was just an example, using a SQL Server date function.
What does FORMAT(create_dtim,'dd-MM-yyyy')do?
Just so you know, FORMAT is 44 times slower than either CAST or CONVERT.

Thks for your help....still find the solution for me 🙁

Regards,

Cachado

Ant-Green SSC Guru Points: 113445 More actions · Answer 9

jcachado (11/27/2015)
Thks for your help....still find the solution for me 🙁
Regards,
Cachado

The error your getting is an Informix error not a Microsoft SQL Server error.

I would suggest posting this on an Informix forum or taking a look in the informix documentation on converting date time fields.

https://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqlr.doc/ids_sqr_171.htm

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 10

jcachado (11/27/2015)
Jeff Moden (11/27/2015)
BrainDonor (11/27/2015)
That was just an example, using a SQL Server date function.
What does FORMAT(create_dtim,'dd-MM-yyyy')do?
Just so you know, FORMAT is 44 times slower than either CAST or CONVERT.
Thks for your help....still find the solution for me 🙁
Regards,
Cachado

I don't even know how to spell "Informix". This is a Microsoft SQL Server site. As others have stated, you should probably look for an "Informix" site/forum to get help with this problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcachado Mr or Mrs. 500 Points: 514 More actions · Answer 11

Still searching...

TO_CHAR(create_dtim, '%m-%d-%y')

Returns the strange error:

ERROR [HY000] [INTERSOLV][ODBC Informix driver][Informix]Unknown error message -19828.

Regards,

Cachado

Ed Wagner SSC Guru Points: 287026 More actions · Answer 12

jcachado (11/27/2015)
Still searching...
TO_CHAR(create_dtim, '%m-%d-%y')
Returns the strange error:
ERROR [HY000] [INTERSOLV][ODBC Informix driver][Informix]Unknown error message -19828.
Regards,
Cachado

TO_CHAR is an Oracle SQL function. This is a SQL Server forum.

If you're querying a SQL Server 2008 database (assumption based on the forum this question is posted in) then try CAST or CONVERT.

https://msdn.microsoft.com/en-us/library/ms187928.aspx

Looking at your error, I've never heard of using an Informix ODBC driver to access SQL Server before. If you're trying to query something other than SQL Server, then I suggest you look for a forum devoted to the appropriate database.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions