Date Time Convertion

Question

Date Time Convertion

nageshp

Right there with Babe

Points: 719
More actions
November 17, 2010 at 12:32 am

#242910

select Getdate() display the below format
2010-11-17 12:59:04:584
Need the Getdate() or any date in the below format
11/17/2010 12:59:04 PM (mm/dd/yyyy time )

Viewing 12 posts - 1 through 11 (of 11 total)

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

why? WHY?

A date is a datetime value, nothing else. What you want is a textual representation of a datetime value and you can accomplish that with CONVERT function. Look in Books Online how to use the CONVERT function.

N 56°04'39.16"
E 12°55'05.25"

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 2

nageshp (11/17/2010)
select Getdate() display the below format
2010-11-17 12:59:04:584
Need the Getdate() or any date in the below format
11/17/2010 12:59:04 PM (mm/dd/yyyy time )

Formatting part should always be done in the front end or in a reporting tool.They usually have a very versatile sets of formatting functions.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

Please see this link http://www.karaszi.com/SQLServer/info_datetime.asp for all you could ever wish to know about dates in SqlServer

Clear Sky SQL
My Blog[/url]

nageshp Right there with Babe Points: 719 More actions · Answer 4

SELECT CONVERT(DATETIME,((SELECT CONVERT (VARCHAR,GETDATE(),101))+' ' +(SELECT SUBSTRING (CONVERT(VARCHAR,GETDATE(),109),14,27 ))))

Output

11/17/2010 2:01:51:833PM

Still am not able to remove the milliseconds as it should be below.

11/17/2010 2:01:51PM

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 5

nageshp (11/17/2010)
SELECT CONVERT(DATETIME,((SELECT CONVERT (VARCHAR,GETDATE(),101))+' ' +(SELECT SUBSTRING (CONVERT(VARCHAR,GETDATE(),109),14,27 ))))
Output
11/17/2010 2:01:51:833PM
Still am not able to remove the milliseconds as it should be below.
11/17/2010 2:01:51PM

select CONVERT(varchar(20),GETDATE(),120)

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

nageshp Right there with Babe Points: 719 More actions · Answer 6

SELECT CONVERT(VARCHAR(20),GETDATE,120)

above query not giving the AM/PM at the end

Please check the below one (ignore the previous one)

SELECT (SELECT CONVERT (VARCHAR,GETDATE(),101))+' ' +(SELECT SUBSTRING (CONVERT(VARCHAR,GETDATE(),109),14,27 ))

Output

11/17/2010 2:01:51:833PM

Still am not able to remove the milliseconds as it should be below.

11/17/2010 2:01:51PM

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 7

One way of doing it is like that:

select convert(char(8),getdate(),103) + ' ' +

CASE WHEN datepart(hh,GETDATE()) > = 12 THEN CONVERT(varchar(10), dateadd(hh,-12,getdate()),8) + 'PM'

else CONVERT(char(10),getdate(),8) + 'AM' end

Of course the real question is why??? The database role should be dealing with the data. The presentation layer should deal with the dates’ format. It seems that you are taking care of something that the report’s generator\application should take care.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 8

SELECTSTUFF(CONVERT(CHAR(10), GETDATE(), 101) + ' ' + SUBSTRING(CONVERT(CHAR(19), GETDATE(), 100), 13, 7), 15, 2, SUBSTRING(CONVERT(CHAR(8), GETDATE(), 8), 4, 5))

N 56°04'39.16"
E 12°55'05.25"

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 9

Hi

With space for AM/PM

SELECT STUFF(CONVERT(CHAR(10), GETDATE(), 101) + ' ' + SUBSTRING(CONVERT(CHAR(19), GETDATE(), 100), 13, 7), 15, 2, SUBSTRING(CONVERT(CHAR(8), GETDATE(), 8), 4, 5))

Without space for AM/PM

SELECT (SELECT convert (varchar,GETDATE(),101))+' ' +(case when datepart(hh,GETDATE()) > = 12 then convert(varchar(10), dateadd(hh,-12,getdate()),8) + 'PM'

else CONVERT(char(10),getdate(),8) + 'AM' end)

Thanks

parthi

Thanks
Parthi