change date to dd/mm/yyyy

Question

Post reply

change date to dd/mm/yyyy

Mad-Dog

SSCertifiable

Points: 6108
More actions
February 4, 2010 at 2:20 pm

#138576

Hi,

i want to retrieve the server uptime so i run this query

exec master..xp_cmdshell 'net STATISTICS server | find "Statistics since"'

this give me the server start time.

the problem is that the server start time that i receive in windows server 2008 and sql server 2008 is m/d/yyyy(today date 2/4/2010 ).

how i can get it to be dd/mm/yyyy?

i need it to be exactly on all server type (2003,2008,2008r2) and on all sql (2005,2008)

THX

Viewing 15 posts - 1 through 15 (of 18 total)

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

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 1

MANU-J.

SSC-Dedicated

Points: 31126

February 4, 2010 at 2:34 pm

#1114024

Better take it in a sql table and format it there.

MJ

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 2

Can you change the Regional setting on the machine or in SQL Server to United Kingdom? That should change your default to dd/mm/yyyy

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

SQLRNNR SSC Guru Points: 281324 More actions · Answer 3

MANU-J. (2/4/2010)

Better take it in a sql table and format it there.

MJ

I would go with this suggestion. Change the format from sql side.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Mad-Dog SSCertifiable Points: 6108 More actions · Answer 4

Mad-Dog

SSCertifiable

Points: 6108

February 4, 2010 at 2:54 pm

#1114034

Stefan Krzywicki (2/4/2010)

Can you change the Regional setting on the machine or in SQL Server to United Kingdom? That should change your default to dd/mm/yyyy

i can't change it permanently.

but can i change it just for the script itself to get the right results without influencethe server?

SQLRNNR SSC Guru Points: 281324 More actions · Answer 5

if you import the date into a datetime field and then use the following:

SELECT convert(varchar,convert(datetime, '2/4/2010'),103)

You should be able to accomplish what you need.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Mad-Dog SSCertifiable Points: 6108 More actions · Answer 6

CirquedeSQLeil (2/4/2010)

if you import the date into a datetime field and then use the following:

SELECT convert(varchar,convert(datetime, '2/4/2010'),103)

You should be able to accomplish what you need.

ok.

if i use this command EXEC master..xp_cmdshell 'net STATISTICS server | find "Statistics since"'

and in i insert the results to a table how can i manipulate the table to get the date and time from the results?

some times the result will be like this in the table

Statistics since 2/4/2010 11:11:43 PM

and sometimes like this

Statistics since 12/24/2010 05:11:43 AM

THX

SQLRNNR SSC Guru Points: 281324 More actions · Answer 7

if you set a default on the field to accept a specific format - it should convert it for you.

Are you capturing more information than that? If you know which servers are associated to which format, you could also build case statements around the two types.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Mad-Dog SSCertifiable Points: 6108 More actions · Answer 8

CirquedeSQLeil (2/4/2010)

if you set a default on the field to accept a specific format - it should convert it for you.

Are you capturing more information than that? If you know which servers are associated to which format, you could also build case statements around the two types.

i going to run this report on a lot of servers.

if i run this syntax "set dateformat dmy" does it effect all server or just the current session?

SQLRNNR SSC Guru Points: 281324 More actions · Answer 9

current session.

From MSDN

This setting affects the interpretation of character strings as they are converted to date values for storage in the database. It does not affect the display of date data type values that are stored in the database or the storage format.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 10

Example:

CREATE TABLE [dbo].[Statsdate] (

[Statsdate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO

declare @pos int

insert statsdate

EXEC master..xp_cmdshell 'net STATISTICS server | find "Statistics since"'

delete from statsdate where statsdate is null

select @pos= patindex('%[0-9]%',statsdate) from statsdate

update statsdate

set statsdate=substring(statsdate,@pos-1,len(statsdate))

SELECT convert(varchar,convert(datetime,statsdate),103) from statsdate

HTH!

SQLRNNR SSC Guru Points: 281324 More actions · Answer 11

SQLRNNR

SSC Guru

Points: 281324

February 4, 2010 at 9:30 pm

#1114171

looks good to me.

I haven't tested that - but i think it could work in this scenario

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Mad-Dog SSCertifiable Points: 6108 More actions · Answer 12

Mad-Dog

SSCertifiable

Points: 6108

February 5, 2010 at 2:21 am

#1114260

can you tell me please on what it deepened in sql server to show the date format like mdy or dmy?

THX

SQLRNNR SSC Guru Points: 281324 More actions · Answer 13

Your regional settings determine the date format. However, that is why there are standards that many use to format the datestamp in a common format for servers they administer that are in different regions.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

DBA in Unit 7 SSCarpal Tunnel Points: 4059 More actions · Answer 14

The command 'net statistics server' seems not so reliable to report the uptime of a server.

Out of curiousity, I ran it on on several servers, the results:

(1) my own XP laptop --showing correct time

(2) two Windows 2008 server: 2/6/2106

(3) another Windows 2008 server: 1/1/1980

Searched online and found that

net statistics workstation | find "Statistics since"

or

systeminfo | find "Boot Time"

would give the precise info.