Different date format in different tables.

Question

Post reply

Different date format in different tables.

Ashwin M N

Hall of Fame

Points: 3668
More actions
December 18, 2007 at 5:10 am

#177639

One of my application developer asked whether it is possible to store
the date format in different table as we desired.
Example:
Table1 (A1 datetime)
YYYY-MM-DD
Table2 (B1 datetime)
DD-MM-YYYY
Table3 (C1 datetime)
MM-DD-YYYY.

Viewing 11 posts - 1 through 10 (of 10 total)

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

haichells SSCrazy Points: 2708 More actions · Answer 1

On doing Inserts / Updates in those tables you need to do a Convert of Datetime with the necessary format and do.

Ashwin M N Hall of Fame Points: 3668 More actions · Answer 2

First tell me how do I change the default format from YYYY-MM-DD to DD-MM-YYYY to a particular table. I know about the usual modification for Insert\Update using convert function.

Jeff Moden SSC Guru Points: 1003925 More actions · Answer 3

For so many reasons, storing dates as formatted VARCHARs is such a terrible idea. If you need to do any date math or comparison of dates, formatted dates only slow down performance.

Tell you developer not to store formatted dates... all dates should be stored as DATETIME and never anything else.

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

Ashwin M N Hall of Fame Points: 3668 More actions · Answer 4

I want to make clear about what my application developer wants.

create table test1 (A1 int identity(1,1),A2 datetime)

insert into test1(A2) select 19-12-2007

create table test2 (A1 int identity(1,1),A2 datetime)

insert into test2(A2) select 12-19-2007

create table test3 (A1 int identity(1,1),A2 datetime)

insert into test3(A2) select 2007-12-19

The date data in the each table should be stored in the same way as the insert.

My application developer asked my about this.

Grant Fritchey SSC Guru Points: 398717 More actions · Answer 5

SQL Server can take the last two formats you supplied and automagically convert them into date time, no issues. The first string you supplied, the more European approach of day/month/year, it doesn't automatically store.

Take a look at the SET DATEFORMAT function in the BOL. It supplies these examples that are probably immediately applicable:

-- Set date format to month, day, year.

SET DATEFORMAT mdy;

GO

DECLARE @datevar DATETIME;

SET @datevar = '12/31/1998';

SELECT @datevar AS DateVar;

GO

-- Set date format to year, day, month.

SET DATEFORMAT ydm;

GO

DECLARE @datevar DATETIME;

SET @datevar = '1998/31/12';

SELECT @datevar AS DateVar;

GO

-- Set date format to year, month, day.

SET DATEFORMAT ymd;

GO

DECLARE @datevar DATETIME;

SET @datevar = '1998/12/31';

SELECT @datevar AS DateVar;

GO

Just remember that this is changing an input method, not a storage method. You'll still be storing the same thing in your columns, so you will need to format the output from each of the different tables.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003925 More actions · Answer 6

Ashwin M N (12/18/2007)
I want to make clear about what my application developer wants.
create table test1 (A1 int identity(1,1),A2 datetime)
insert into test1(A2) select 19-12-2007
create table test2 (A1 int identity(1,1),A2 datetime)
insert into test2(A2) select 12-19-2007
create table test3 (A1 int identity(1,1),A2 datetime)
insert into test3(A2) select 2007-12-19
The date data in the each table should be stored in the same way as the insert.
My application developer asked my about this.

Your application developer is asking to do things the wrong way. You simply must not store formatted data whether it be numbers or dates... it will cause great pain for the application and all developers that follow. All date data must be stored as the DATETIME datatype. You can format the returns of a Select, if you'd like, but even that is wrong in today's global economy... let the application format the returns based on the local date/time settings. There are, quite literally, thousands of posts that warn against storing formatted dates in Varchar or Char. Shoot, you can even make a calculated column that will format the date, if you'd like... but you must not store the base data in a formatted Varchar.

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

Grant Fritchey SSC Guru Points: 398717 More actions · Answer 7

[beating head against desk] must read complete post, must read complete post...

Anyway, Jeff's 100% accurate. I didn't see that "must be stored" statement. You can't, don't, won't, shouldn't store the stuff in that manner. You need to store a datetime, period. Formatting is something else and can be done wherever and whenever it's appropriate.

Sorry I missed that. Thanks Jeff.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003925 More actions · Answer 8

Heh... not to worry, Grant... I have a target on my desk, right next to my coffee cup, that says "Pound head here, then read the post again." I've done the same thing many a time...

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

StarNamer SSCrazy Eights Points: 8746 More actions · Answer 9

For most of what I need I use SMALLDATETIME and await the opportunity to use 2K8's DATE type!

Derek

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

You should wait forever for that... it's just another way to screw up dates and times.

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