Postgresql date format problem

1

Hello, I created a table with the date:

But, at least when running Visual Studio 2013, it forces the timestamp to show with it. All I want is the actual date, 10/12/2013 or such.

The PostGreSQL table shows plain 10/12/2013 with no timestamp.

Any idea why it shows a timestamp? Thanks.

2

If the timestamp shown is 00:00:00:.000, it is an artifact of the presentation layer (in this case, the VS2013's component that returns the result to you). If the data type is DATE, it stores only the date portion, not the time portion.

You can format the date to suppress the time portion for presentation purposes if you wish to do so, but for the purposes of data storage and manipulation, you wouldn't need to be concerned about it.

3

Thanks, I finally figured out the data as fine in the table, but VS 2013 does not show it without sticking on a timestamp! The PostgreSQL has yyyy-mm-dd order with no timestamp but VS shows in the gridview mm/dd/yyyy 12:00:00 AM.

So is there a way to take out the time portion so only the date shows up? Some sort of string manipulation?

I tried DateTime.ParseExact with format = "d/M/yyyy h:mm tt";

but the formats won't coincide to what is in the string.

I use a cmd.CommandText = "SELECT check_date, check_number, post_date, check_amount FROM revenue WHERE id_owner = "

to pick up the dates and a while (dr.Read()) to load the ListBox.

So I end up getting a Listbox with date and time for both dates when I just want the date.

Thanks

4

SQL Server should be returning a SqlDbType = Date. See here. You can use reader.GetDataTypeName and reader.GetFieldType to determine the types on both sides. Assuming you are getting Date, and if it is still showing with the time portion, you should format the grid or listbox.

5

DATE (as distinct to DATETIME) is a relatively new Data Type in MS SQL. As such maybe something in the middle!! is treating it as DATETIME and appending a "midnight" time onto it.

Perhaps some patches / new versions / etc. would make that data-mangling go away?

(DATE is not THAT new ... SQL 2008 I think?? but ... I still find there is loads of legacy stuff lying around that doesn't handle it properly)

6

Well now I've tried an insert but it freaks (got all the rest to work.)

It gives error "column "check_date" is of type date but expression is of type integer"

Now check_date is set as 'date'

The sql statement has:INSERT INTO revenue (id_company, id_owner, check_date, check_number, check_amount, post_date) VALUES (3434,5223,12/12/2015,3231,784.32,01/01/2015)

For some reason 12/12/2015 is a nogo. I have done lots of research and still cannot figure out why.

The table has check_date with data such as 4/7/2014 residing in it. All are in that order. Now I tried the key field, which is auto incremented, and have a number assigned but got the same error (and thus the fields are in sync.)

Clues?

7

That is 12 divided by 12 divided by 2015 ...

Dates need to be quoted strings, and should only ever be presented in the unambiguous format 'yyyymmdd' - i.e. for your example

'20151212'
8

Well I WAS in this quandary. POSTgresql as a date definition. Comes out fine. 10/12/2015 in the table.

But MS C# has DateTime.. 10/12/2014 12:00:00 AM... yes it adds the time but the gresql does not recognize that time..It blows and gives the error the 12: is not recognized.

I don't want time added to the table due to all the gridboxs would have the time stuck in then as well as date.

So here is the way I just figured out!

Use a MaskedTextBox and set it up for short date.

Then once the date is entered and you click the insert record button

DateTime xcheck_date = Convert.ToDateTime(txtPayments_chkDatem.Text); //txtPayments_chkDatem is the masked textbox.

Then when constructing the SQL statement put "'" around the data to be sent.

...+ "'" + xcheck_date.ToString() + "'" + ","... now the SQL will see the '02/03/2015' show up as valid for input for a date field.

As I'm famous for saying.." it's merely a matter of programming."

Thanks for your help!

9 
SELECT CONVERT(varchar(10), GetDate(), 101)

will give you 'mm/dd/yyyy' - the "varchar(10)" will constrain the output to the first 10 characters only (although the "101" format directive only gives you the date anyway, there are other format directive values that provide a variety of date/time styles).

However, SQL will provide this as a "Text String", not a date. That might be fine for your needs, but it is always better to get the data in a native datatype format, if possible ..., so that the underlying language can handle the data correctly without [the dangers of!] parsing etc.

But might help with the situation you are in - just a matter fo programming, as you so correctly said :slight_smile: