How to convert date into date time formate in postgres

Question

1.00/5 (1 vote)

See more:

Hi,

I have this format date in source database as date type date.

"2016-07-10"

I need to convert it to below format

1943-11-05 00:00:00.000

What I have tried:

I have try below query for it

cast(column1 as timestamp)

But it gives me below result

"2016-07-10 00:00:00"

That is not perfect as per my requirement.

Posted 21-Apr-21 23:23pm

Member 11776570

Updated 22-Apr-21 0:03am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2021-04-22T00:03:00

Solution 1

If your input value is 2016-07-10, why would you expect the output to be a completely different date?

Your actual output is exactly what you would expect given your input.

If you want to change the value to a different date, then you're going to have to explain how you calculate the output from the input.

Posted 22-Apr-21 0:03am

Richard Deeming

Comments

Member 11776570 22-Apr-21 6:25am

I have to convert it to datetime because data is convert from source system to destination system. So destination system has datetime data type in it.

Richard Deeming 22-Apr-21 6:27am

And you have converted it to datetime. The only "problem" in your question is that the datetime uses the same date as the input, whereas your expected output uses a completely different date.

You need to re-read your question, and explain what the problem is.

Member 11776570 22-Apr-21 6:46am

I just need to convert this 2016-07-10 date format to like this 1943-11-05 00:00:00.000 one noting else. In PostgresSQL

Richard Deeming 22-Apr-21 6:48am

Which is exactly what you have done already!

The only "problem" you have is that you are expecting an input of 2016-07-10 to produce an output of 1943-11-05, but you haven't explained WHY you expect that, nor HOW you think the two values are related.

Member 11776570 22-Apr-21 7:07am

Okay i got your point. Sorry my mistake.
It has date this 2016-07-10 need to convert it to 2016-07-10 00:00:00 in this format.

Richard Deeming 22-Apr-21 7:12am

OK, so what is the problem? Your code converts the value to the expected value.

timestamp is the correct type to use:
PostgreSQL: Documentation: 9.1: Date/Time Types[^]

You could play with the "precision" argument for the timestamp type. But since your input is a date with no time, it's not going to change the value of the output.

Member 11776570 22-Apr-21 6:26am

I need to directly change it to datetime data type.