Date Format

Question

Post reply

Date Format

t-lynn

SSC Journeyman

Points: 78
More actions
July 10, 2002 at 1:14 pm

#63175

I am curious to know if there is a setting in SQL for the default date format. I need the date to show as dd-mmm-yy instead of mm/dd/yy. I've set the server's regional date settings to dd-mmm-yy but it doesn't seem to register that setting.
Any help would be great.

Viewing 5 posts - 1 through 4 (of 4 total)

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

jamyer Default port Points: 1433 More actions · Answer 1

You will need to roll your own.

You can do something like

select convert(char (9),getdate(),6)

where you would replace getdate() with your datetime variable. And then if needed assign the result to a variable and stuff in the dashes.

If you have a need for the date to appear a certain way in a lot of places look into UDF's. See the udf_MyDate.sql example that ships with SQL 2000.

danw SSCommitted Points: 1842 More actions · Answer 2

The only way to get what you want is to convert the date with a style.

What your looking for is the Italian standard which is a style of 5 without the century and 105 with the century.

For more information on styles look up CONVERT.

convert(varchar(10),getdate(),105)

The other optionis to use the SET DATEFORMAT command but that will only support displaying the date with slashes (/) and is on a per connection basis.

SET DATEFORMAT mdy

If you have a few tables that particularly annoy you you can create views with the date converted to the style that you need.

Antares686 SSC Guru Points: 125444 More actions · Answer 3

These are configurable both at the server level for default and the individual users based on language otherwise DATEFORMAT allows specific formats for entry. To set at the server level, open in EM then right click the server you wish to set and choose properties then server settings and change to the laguage that uses the dateformat you want. Or you can use sp_defaultlanguage to set for individual users. Now if you don't want to change the language use CONVERT and the format you want. ALl information is available in SQL BOL.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

cafemar1 SSCrazy Points: 2405 More actions · Answer 4

Just a quick note. At the end of the convert you can see the 105? Well, you can change this one to 106 the date will show as 15 Jul 2002. You can test this from 100 to 107 and use the one you need.