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

ChetShah
Starting Member

37 Posts

Posted - 2005-09-12 : 05:41:25

Hi,

I've checked the Server Date Format in EM within server configuration and its set to British English. However in Query analyser, although the result set is set for regional options (which in my case are UK settings) , when you query a date field its returning american format - MM/DD/YYYY

Any ideas on where to check for correct date formats??

Chet

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-12 : 05:46:48

maybe you need
set dateformat dmy
?

data is stored in sql server like 2 integers anyway, so formating is a presentation layer issue.

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

Kristen
Test

22859 Posts

Posted - 2005-09-12 : 07:47:45

My PC is set to UK locale.

Using
select GetDate()

With "Regional options turned off I get

------------------------------------------------------
2005-09-12 12:45:36.680

With them turned ON I get:

------------------------------------------------------
12/09/2005 12:44:26

The server I am connected to delivers file dates, in Windows Explorer, in m/d/y format, so I'm pretty sure that it is set to "American" - so it looks as though my Regional Settings in Q.A. are having an effect ... if that's any help?

Now all I need to do is persuade Q.A. that a date does not have to be 54 characters wide - anyone know how to solve that one?

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-12 : 10:31:26

quote:

Now all I need to do is persuade Q.A. that a date does not have to be 54 characters wide - anyone know how to solve that one?

Kristen

emmm.... huh??
what do you mean?

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

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-09-12 : 22:01:45

This looks like a good spot for one of my stupid scripts. lol


SET NOCOUNT ON

DECLARE
	@min INT,
	@max INT,
	@date DATETIME

SELECT
	@min = 1,
	@max = 131,
	@date = GETDATE()

SELECT @date

WHILE @min <= @max
BEGIN

	IF @min BETWEEN 15 AND 19 
		OR @min  = 26 
		OR @min BETWEEN 27 AND 99
		OR @min BETWEEN 115 AND 119
		OR @min BETWEEN 122 AND 125
		OR @min BETWEEN 127 AND 129
		BEGIN
			GOTO NEXT_LOOP
		END

	SELECT @min, CONVERT(VARCHAR,@date,@min), 'SELECT CONVERT(VARCHAR,GETDATE(),' + CAST(@min AS VARCHAR(5)) + ')'

NEXT_LOOP:

SELECT @min = @min + 1
END

I hope this guy replies. I would like to know what he meant also spirit.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2005-09-13 : 05:23:39

"emmm.... huh??
what do you mean?"

Well if you can fix it for me I'll be very grateful.

In QA, with Text Results mode set, if I type

select GetDate()

I get

------------------------------------------------------
2005-09-12 12:45:36.680

There are 54 dashes, Why's that then?

This means that the width of date columns when I do a quick SELECT * type query is 54 characters

.

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-13 : 05:30:48

select len(convert(varchar(100),GetDate())) also returns 54?
same thing happens to me to...
why is that a problem for you?

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-13 : 05:32:07

I dont understand that dash behaviour

Select len('September 13 2005 15:09:13.787') as Test

I get 11 dashes

Test
-----------
30

(1 row(s) affected)

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-13 : 05:39:59

>>why is that a problem for you?

to me or Kris?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-09-13 : 05:53:37

select len(convert(varchar(100),GetDate()))

-----------
19

(1 row(s) affected)

"why is that a problem for you?"

Its just that date columns in a SELECT TOP 10 * type query are overly wide (I appreciate I can use GRID format)

Its probably particularly a problem for me because all our tables have a Create Date and Update Date column right at the start of the column list - so whenever I do some sort of SELECT * to have a look at the records the Create/Update Date columns are wasting screen real estate!

"I get 11 dashes"

That's for an Int result column - how many do you get for SELECT GetDate() ?

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-13 : 06:01:22

>>how many do you get for SELECT GetDate() ?

54

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources