Date format is getting change in server

Question

Hi i am taking date from db where date column is of type 'Date' then splitting it and concatenating it in a string to store in db

Protected Sub btClick(sender As Object, e As EventArgs)
Dim dtMembers As DataTable = ViewState("dtMembers")
For i As Integer = 0 To dtMembers.Rows.Count - 1
                    For j As Integer = 0 To dtMembers.Columns.Count - 1
                        strMembers = strMembers + dtMembers.Rows(i)(j).ToString() + "#"
                    Next
                    strMembers = strMembers.Substring(0, strMembers.Length - 3) + "|"
                Next
                strMembers = strMembers.Substring(0, strMembers.Length - 1)
''then inserting it in db
End sub

while executing the program from local enviornment it works fine and time store as 28-09-2021 15:34:42 while executing the program through server it stored in 12 hour format 28-09-2021 03:34:42PM which creates problem and throws error . Any idea how to resolve this issue will be appreciated.

@OldProgrammer Sql = "INSERT INTO pbl(dtval) values ('" + strMembers+ "') date is one of concatenated member in strMembers — Shivam, Sep 29 '21 at 12:24
@Shivam Please do **NOT** build SQL statements using string concatenation; use [bind variables](https://docs.oracle.com/cd/B25329_01/doc/appdev.102/b25312/building_odp.htm#CEGCGDAB). — MT0, Sep 29 '21 at 12:26
Why in the world would you take a DATE (_"taking date from db where date column is of type 'Date"_) Then "split it" (presumably splitting apart the date and the time components that are inherent in a DATE data type), then concatonate them back togehter into a _string_, and store that string back into some other column? First, it's wasted effort, and second, storing a DATE as anything other than DATE data type (which you say you already have) is a very serious design flaw. — EdStevens, Sep 29 '21 at 23:11

MT0 · Answer 1 · 2021-09-29T12:30:04.510

In Oracle, a DATE data type is stored in a binary format consisting of 1 byte each for century, year-of-century, month, day, hour, minute and second.

A DATE data type does NOT store any format information.

For example:

CREATE TABLE table_name ( date_column DATE ); 

INSERT INTO table_name ( date_column ) 
VALUES ( TO_DATE( '28-09-2021 15:34:42', 'DD-MM-YYYY HH24:MI:SS' ) );

SELECT DUMP(date_column)
FROM   table_name;

Outputs:

DUMP(DATE_COLUMN)

Typ=12 Len=7: 120,121,9,28,16,35,43

The raw date value is 7 bytes:

Century + 100
Year-of-century + 100
Month
Day
Hour + 1
Minute + 1
Second + 1

Nothing there tells you how to format a DATE when it is displayed and the database will just know those binary values.

If you want to display a DATE then the format it is displayed in is entirely up to the client application you are using.

For example, in SQL/Plus or SQL Developer, it is controlled by the user's session parameters and the default display format depends on where you are in the world.

In 3rd-party applications, the display format is nothing to do with the database; so you will need to set that format in VB (usually programmatically in your application).

While executing the program through server it stored in 12 hour format 28-09-2021 03:34:42PM

No, as demonstrated above, it doesn't store any formatting information and is not stored as a 12-hour clock.

You need to format it into your desired format when you retrieve the date.

Sql = "INSERT INTO pbl(dtval) values ('" + strMembers+ "')"
date is one of concatenated member in strMembers

It is bad practice to build queries using string concatenation as it leads to SQL injection issues.

Instead, your statement should be:

Sql = "INSERT INTO pbl(dtval) values (:date_value)"

And then you should add the parameter values as bind variables.

Date format is getting change in server

1 Answers1