date format - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

date format

Author

Topic

poornima
Starting Member

32 Posts

Posted - 2006-11-13 : 05:11:49

How to display the date in a specified format.

I executed the command in SQL Server 2005

Insert into AccountsBalance(Date) VALUES(05-11-2006)
But when i retrieved the database and displayed it in a datagrid it is displaying as

29-Jun-94
and the datatype of Date is datetime

Thanks In Advance
Poornima

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-13 : 05:13:24

Change the format of the datagrid, not the insert to the database.

Peter Larsson
Helsingborg, Sweden

poornima
Starting Member

32 Posts

Posted - 2006-11-13 : 05:37:22

Even in database it is dispaying in different format like

1894-06-29 00:00:00.000

is there any method to convert into a correct format

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-13 : 05:38:35

No.
DON'T WORRY ABOUT HOW DATETIMES ARE PRESENTED IN BACKEND.
FIX THE VISUAL STYLE (FORMAT) OF DATETIMES IN FRONT-END (© 2K6 Madhivanan)

Peter Larsson
Helsingborg, Sweden

poornima
Starting Member

32 Posts

Posted - 2006-11-13 : 05:41:51

in the front end how do you convert it

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-13 : 05:44:26

You set the FORMAT property for that column or entire grid,
to the specific format you want to display a date in.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-13 : 06:31:33

quote:
Originally posted by poornima

in the front end how do you convert it

In VB6, there is format function
Format(date,"dd-mm-yyyy")
Format(date,"mm-dd-yyyy")
etc

Also when sending date to table send it in YYYYMMDD format to avoid conflict with local settings. Also you should consider using Stored procedure with Datetime parameter

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-13 : 06:37:03

quote:
Originally posted by Peso

No.
DON'T WORRY ABOUT HOW DATETIMES ARE PRESENTED IN BACKEND.
FIX THE VISUAL STYLE (FORMAT) OF DATETIMES IN FRONT-END (© 2K6 Madhivanan)

Peter Larsson
Helsingborg, Sweden

Thanks Peso

Madhivanan

Failing to plan is Planning to fail

madhuotp
Yak Posting Veteran

78 Posts

Posted - 2006-11-13 : 08:15:24

Hi,

You can get the date format as u want from backend by using convert function

eg..
select convert(varchar(10),getdate(),105)

But in your case , i think this setting is to be done at the application level...

Madhu

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-13 : 08:17:29

quote:
Originally posted by madhuotp

Hi,

You can get the date format as u want from backend by using convert function

eg..
select convert(varchar(10),getdate(),105)

But in your case , i think this setting is to be done at the application level...

Madhu

Dont use convert function in sql when dates are displayed in front end application

Madhivanan

Failing to plan is Planning to fail

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-11-13 : 13:25:09

I am afraid you all misunderstand the poor guy/gal (no pun intended):

Insert into AccountsBalance(Date) VALUES('05-11-2006')

or preferably, so you dont depend on language setting:

Insert into AccountsBalance(Date) VALUES('20061105')

what the silly server does, is addining the numeric expression 5-11-2006 (= -2012) and then convert that to days since '19000101' which gives an odd day in the 1890s.

-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter."

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-13 : 16:08:26

Oh, that's just a typo. In the line after, he says the insert is ok, but he result is formatted the "wrong way"...

Peter Larsson
Helsingborg, Sweden

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-11-13 : 18:06:05

The output of

SELECT CONVERT(datetime, -2012)

looks oddly familiar to

quote:
1894-06-29 00:00:00.000

-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-13 : 19:53:10

quote:
Originally posted by Peso

Oh, that's just a typo. In the line after, he She says the insert is ok, but he result is formatted the "wrong way"...

Peter Larsson
Helsingborg, Sweden

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-13 : 19:57:14

quote:
Originally posted by PSamsig

The output of
SELECT CONVERT(datetime, -2012)
looks oddly familiar to
quote:
1894-06-29 00:00:00.000

-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter."

So as these
Select cast(2005 as datetime)
Select cast('2005' as datetime)

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-13 : 22:51:12

Then her problem is simply that she forgot to put quotes around date value while inserting:

Insert into AccountsBalance(Date) VALUES('05-11-2006')

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-14 : 08:26:35

quote:
Originally posted by harsh_athalye

Then her problem is simply that she forgot to put quotes around date value while inserting:

Insert into AccountsBalance(Date) VALUES('05-11-2006')

Harsh Athalye
India.
"Nothing is Impossible"

Yes. Single quotes make big difference as shown in my example
But I always expect to get error for
Insert into AccountsBalance(Date) VALUES(05-11-2006)
saying invalid systax near '-'

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-14 : 08:45:38

Not in this case!

That's the cost you have to pay for SQL Server's extra smartness (or so called user-friendliness) !

or you have to be extra-alert while doing such operations.

declare @t table
(
	dt datetime
)

insert into @t values(05-11-2006)

select * from @t

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-14 : 09:35:44

>>or you have to be extra-alert while doing such operations.

I agree with you

Madhivanan

Failing to plan is Planning to fail

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-11-14 : 09:56:03

But if you use the universal format

SELECT CONVERT(datetime, 20061106)

then you get

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

(1 row(s) affected)

so one more reason to always do that

-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter."

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-14 : 10:42:12

for heaven's sake just use parameters!!!!!

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources