January 22, 2010 at 1:26 pm
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
12 01/1/2010 1/1/2010
12 01/1/2010 01/1/2010
12 01/2/2010 1/2/2010
12 01/2/2010 01/2/2010
12 01/3/2010 01/3/2010
12 01/4/2010 01/4/2010
What should Add to my query to get the date DD/MM/YYYY HH:MI:SS:MMMAM
January 22, 2010 at 1:31 pm
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
12 01/1/2010 1/1/2010
12 01/1/2010 01/1/2010
12 01/2/2010 1/2/2010
12 01/2/2010 01/2/2010
12 01/3/2010 01/3/2010
12 01/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?
January 22, 2010 at 1:46 pm
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
January 22, 2010 at 1:57 pm
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
January 22, 2010 at 2:16 pm
This is in H24 instead of AM, but maybe this will help.
SELECT (CONVERT( VARCHAR(25), getdate(),103) + ' ' + Convert(VARCHAR(25), getdate(), 114))
TJP8
January 23, 2010 at 11:57 am
This will do it...
SELECT CONVERT(CHAR(10),GETDATE(),103) + ' ' + RIGHT(CONVERT(CHAR(26),GETDATE(),109),14)
--Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply