wrong 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

SQL Server 2000 Forums

SQL Server Development (2000)

wrong Date format

Author

Topic

patshaw
Posting Yak Master

177 Posts

Posted - 2007-02-28 : 06:24:35

Hi ,

When I use:

set dateformat dmy
UPDATE dbo.MyTable
SET EntryDate = CONVERT(Varchar, GETDATE(), 103)

it inserts the date 2/28/2007 which I guess is US format. I want it to insert as 28/02/2007 UK format. Why is this happening and how can I resolve it?

Thanks.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-28 : 06:27:39

1. Don't use character data types to store dates
2. Don't worry about in which format date is internally stored
3. Format only matters when displaying dates
4. Formatting should be done in front-end.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-28 : 07:06:26

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-28 : 13:16:20

Till the time Madhi is not here, somebody should take his charge, right?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-02-28 : 13:29:12

Harsh to the rescue!!

and he's also being harsh!!

Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-02-28 : 14:39:12

quote:
Originally posted by harsh_athalye

1. Don't use character data types to store dates
2. Don't worry about in which format date is internally stored
3. Format only matters when displaying dates
4. Formatting should be done in front-end.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

very nice!

- Jeff
http://weblogs.sqlteam.com/JeffS

rlaubert
Yak Posting Veteran

96 Posts

Posted - 2007-02-28 : 15:28:31

Back to the original problem... formating the date.. you are correct in the use of convert. Check the books on line for the proper format. 103 is dd/mm/yyyy format. But you need to have a size for your varchar datatype. That maybe why it is using the default US data format.

Raymond Laubert
MCDBA, MCITP:Administration, MCT

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-02-28 : 16:25:28

The questions are:

1) What datatype is EntryDate?*

2) Why are you using Convert? To remove the time portion?

How you ultimately end up doing this is depends on those two things, but overall you should just about never CONVERT() dates to VARCHAR's within SQL Server, you are always better off working with proper datetime values and letting your client layer do all of the formatting. Even setting the time of a datetime to 12:00:00AM doesn't require any formatting at all, just a few simple date formulas; introducing formatting into your T-SQL and converting things back and forth from datetime to varchar back to datetime is completely unnecessary and a very bad practice to get into.

* Please tell me the answer is DateTime ...

or we really have a lot of work to do ...

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources