Display date in DD/MM/YYYY HH:MI:SS:MMMAM

Question

Display date in DD/MM/YYYY HH:MI:SS:MMMAM

rambilla4

SSCrazy Eights

Points: 8715
More actions
January 22, 2010 at 1:26 pm

#209364

Hi,
I', writing a query as below and I want to display the time in format DD/MM/YYYY HH:MI:SS:MMMAM
Right now, I'm getting only the date without time as below:
select * from Mydb.dbo.users
where propertyid =12
and a.objectid = b.objectid
and value between '1/1/2010' and '1/10/2010'
ObjectID Value ValueSTD
1201/1/2010 1/1/2010
1201/1/2010 01/1/2010
1201/2/2010 1/2/2010
1201/2/2010 01/2/2010
1201/3/2010 01/3/2010
1201/4/2010 01/4/2010
What should Add to my query to get the date DD/MM/YYYY HH:MI:SS:MMMAM

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

rambilla4 (1/22/2010)
Hi,
I', writing a query as below and I want to display the time in format DD/MM/YYYY HH:MI:SS:MMMAM
Right now, I'm getting only the date without time as below:
select * from Mydb.dbo.users
where propertyid =12
and a.objectid = b.objectid
and value between '1/1/2010' and '1/10/2010'
ObjectID Value ValueSTD
1201/1/2010 1/1/2010
1201/1/2010 01/1/2010
1201/2/2010 1/2/2010
1201/2/2010 01/2/2010
1201/3/2010 01/3/2010
1201/4/2010 01/4/2010
What should Add to my query to get the date DD/MM/YYYY HH:MI:SS:MMMAM

Is the above query your actual query or just a simplified one? Can you provide the DDL (CREATE TABLE statment) for the table?

tjp8 SSCommitted Points: 1989 More actions · Answer 2

This should be what you are looking for

SELECT CONVERT(VARCHAR(25), GETDATE(), 131)

GO

You could try something like this

select a.object_id, convert(varchar(25), a.value, 131)

from Mydb.dbo.users

You can get a list of of the Formats in BOL under CAST and CONVERT or by searching the web for SQL Server Date Formats

TJP8

tjp8 SSCommitted Points: 1989 More actions · Answer 3

Sorry, I overlooked that, that format is apparently a Kuwait Standard format, and although the format is how you want it, the date itself does not come out the same.

When I ran it with Getdate() it came out as 7/02/1431 3:57:09:987PM

TJP8

tjp8 SSCommitted Points: 1989 More actions · Answer 4

This is in H24 instead of AM, but maybe this will help.

SELECT (CONVERT( VARCHAR(25), getdate(),103) + ' ' + Convert(VARCHAR(25), getdate(), 114))

TJP8

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

This will do it...

SELECT CONVERT(CHAR(10),GETDATE(),103) + ' ' + RIGHT(CONVERT(CHAR(26),GETDATE(),109),14)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)