Date Format

Question

Post reply

Date Format

gilabean

SSC Enthusiast

Points: 120
More actions
February 15, 2002 at 10:37 am

#158163

Hi there,
I had been trying out this for days but I just can't find a wat to convert the format stored in the database. I'm using SQL Server 7 and planning to migrate to SQL Server 2K.
The Default format for Date is : mm/dd/yyyy
The format that I want is : dd/mm/yyyy
is it possible for me to change date format that according the the one I had written up there for both the version of SQL?
Thanks in advance.
Simon

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

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

Have you checked the language settings? Looks like this handles the date settings.

Steve Jones

steve@dkranch.net

Antares686 SSC Guru Points: 125444 More actions · Answer 2

It if the field is datetime you may be able to do CONVERT(VARCHAR,FLD,101) to get it to work. Otherwise if the language setting doesn't help you may have to parse and concatinate into the order you want.

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Actually I did it backwards I was giving you mm/dd/yyyy Jim is right except change to CONVERT(VARCHAR,FLD,103) 3 gives yy and 103 gives yyyy is the only difference. My bad been fighting a server all day and just found the problem.

gilabean SSC Enthusiast Points: 120 More actions · Answer 4

I'm very sorry about this..kind of dumb question

but what do you mean by "the language setting"?

You're talking about the programming language that I'm

using?

Antares686 SSC Guru Points: 125444 More actions · Answer 5

No the SQL Server setting. If you right click on the SQL Server in question in Enterprise Manager one of the tabs will have a language setting at the top (sorry I forgot which one). For the current format it is most likely set to english change to british-english and click ok, then stop and restart the server to make sure takes effect. This should cause SQL to change the storage output to the format you want.

gilabean SSC Enthusiast Points: 120 More actions · Answer 6

hhhmm...I tried to change the language setting..but the

same kind of date format came out.

it's still mm/dd/yyyy.

anyway...thanks for helping.

anymore ideas? I had been struggling hard here~

LoL.....xp

wish me luck.

Antares686 SSC Guru Points: 125444 More actions · Answer 7

Again you could parse the date, something like this:

SUBSTRING(CONVERT(CHAR(10),dateFld),4,2) + '/' + LEFT(CONVERT(CHAR(10),dateFld),2) + '/' + RIGHT(CONVERT(CHAR(10),dateFld),4)

This should do it.

GRN Hall of Fame Points: 3260 More actions · Answer 8

hello,

first of all have you checked this from the frontend point of view.

if you're running IIS check the regional settings of the server , if not the settings on the client. SQL server stores dates as number of days from a jan 1 1973 , you cannot change the way it stores dates but only how it interprets it!

hope this helps

gilabean SSC Enthusiast Points: 120 More actions · Answer 9

Hey Guys,

guess I finally gave up on finding out how to store the format i want in the

DB. Hhmm...guess what GRN said is right, can't change the way it stores the

data but can manipulate it.

I found out something and it might be useful for those who's doing some date

comparison. You can actually tell your program to take the date out from the DB and

put it into a format that you want by executing this command

SET DateFormat YourDateFormat ( eg : SET DateFormat dmy )

then no matter how the dateformat in the db are but when it take it out from the

DB, it will automatically convery to the dateformat that you had specified.

hope that this help....

🙂

nigelrivett SSCertifiable Points: 5472 More actions · Answer 10

You are trying to use English format dates. With v6 if the server or operating system were installed as American then sql server would default to mm/dd/yyyy and it was difficult to override. If they were installed as anything else then it was possible to override happily. Ever since then I have been dubious about the date formatting although it does seem better now.

It shouldn't matter though - the date format is just a presentation thing. You should be storing dates as datetime fields and interfacing as datetime if the client can cope. If the client needs a character format transfer as yyyymmdd (you can use dd mmm yyyy if you are English only). If the client needs dd/mm/yyyy or mm/dd/yyyy (and cannot be changed) then make the interface a character field so that you are in control of what happens and expicitly convert in sql at all times. It won't add much effort to the stored procedures and will make sure that you are not at the mercy of installation parameters.

Cursors never.
DTS - only when needed and never to control.