DATETIME 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

DATETIME FORMAT

Author

Topic

avmreddy17
Posting Yak Master

180 Posts

Posted - 2007-12-01 : 20:18:29

What is the easy way to get the below data and Time Format.

11/14/2007 3:51:49 PM

Thanks
Venu

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-12-01 : 20:37:05

Didnt understand your question...can you rephrase?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

avmreddy17
Posting Yak Master

180 Posts

Posted - 2007-12-01 : 22:34:45

SELECT GETDATE() RETURNS
2007-12-01 22:31:52.160

But I need the below format , If some has a function to get the below
format.....

12/01/2007 10:31:52 PM

Thx
Venu

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-12-01 : 22:43:03

You need to use your front end application to do the formatting. If you absolutely have to do in SQL Server have a look at CAST/CONVERT functions in books online.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

avmreddy17
Posting Yak Master

180 Posts

Posted - 2007-12-02 : 08:34:56

Thanks Dinkar.. I am aware of CAST CONVERT Functions. But there is no straight style to convert this.

I am using CONVERT , DATEPART functions to get my format.
Also If you see the Hrs in the date time Column its in the
military format ( 24 Hrs ) . But I am using a CASE Statement
to convert into 12 Hrs format ( ex like 13 to 01 ) and denote the
time as AM or PM

Is there a better way to do this

DATETIME Column-----------> Required OUTPUT Style
2007-11-29 09:40:04.000 - > 11/29/2007 09:40:04 AM
2007-11-29 12:59:58.000 - > 11/29/2007 12:59:58 PM
2007-11-29 12:59:58.000 - > 11/29/2007 12:59:58 PM
2007-11-29 13:00:01.000 - > 11/29/2007 01:00:01 PM
2007-11-29 13:00:01.000 - > 11/29/2007 01:00:01 PM

I am doing some thing like this , but just wanna know if there is a better way..

SELECT TransactionTime ,
CONVERT ( VARCHAR(10) , TransactionTime , 101 ) + ' ' +
CASE WHEN DATEPART( hh , TransactionTime ) = 13 THEN '01'
WHEN DATEPART( hh , TransactionTime ) = 14 THEN '02'
WHEN DATEPART( hh , TransactionTime ) = 15 THEN '03'
WHEN DATEPART( hh , TransactionTime ) = 16 THEN '04'
WHEN DATEPART( hh , TransactionTime ) = 17 THEN '05'
WHEN DATEPART( hh , TransactionTime ) = 18 THEN '06'
WHEN DATEPART( hh , TransactionTime ) = 19 THEN '07'
WHEN DATEPART( hh , TransactionTime ) = 20 THEN '08'
WHEN DATEPART( hh , TransactionTime ) = 21 THEN '09'
WHEN DATEPART( hh , TransactionTime ) = 22 THEN '10'
WHEN DATEPART( hh , TransactionTime ) = 23 THEN '11'

ELSE
CASE WHEN LEN( CAST( DATEPART( hh , TransactionTime ) AS VARCHAR(2) ) ) = 1
THEN '0' + CAST( DATEPART( hh , TransactionTime ) AS VARCHAR(2) )
ELSE CAST( DATEPART( hh , TransactionTime ) AS VARCHAR(2) ) END END + ':' +
CASE WHEN LEN( CAST( DATEPART( mi , TransactionTime ) AS VARCHAR(2) ) ) = 1
THEN '0' + CAST( DATEPART( mi , TransactionTime ) AS VARCHAR(2) )
ELSE CAST( DATEPART( mi , TransactionTime ) AS VARCHAR(2) ) END + ':' +
CASE WHEN LEN( CAST( DATEPART( ss , TransactionTime ) AS VARCHAR(2) ) ) = 1
THEN '0' + CAST( DATEPART( ss , TransactionTime ) AS VARCHAR(2) )
ELSE CAST( DATEPART( ss , TransactionTime ) AS VARCHAR(2) ) END + ' ' +
RIGHT( CONVERT ( VARCHAR(20) , TransactionTime , 100 ) , 2 )
FROM #T1
ORDER BY TransactionTime

Thx
Venu

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-02 : 09:53:31

I agree with the part about formatting this on the frontend but...
you're right that there is not a single canned format to match desired output but one way could be to combine them. Something like:


declare @d datetime
set @d = '2007-12-01 22:31:52.160'

select	convert(varchar, @d, 101) + ' '  --day part
	+ substring(convert(varchar, @d, 109), 13, 8) + ' '  --time part with a trailing space
	+ right(convert(varchar, @d, 109), 2) --am/pm part 

output:
12/01/2007 10:31:52 PM

Be One with the Optimizer
TG

avmreddy17
Posting Yak Master

180 Posts

Posted - 2007-12-02 : 10:32:41

Thanks TG... This is much simpler than what I have. But only thing is wiht the time part with the Trainling space.
I need with the Trailing Zero.

There is no front end. These stored Procs are used to create reports in Excel.

Thanks Again for sharing this..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-02 : 10:35:24

[code]
DECLARE @d datetime
SET @d = '2007-12-01 22:31:52.160'

SELECT CONVERT(varchar(10), @d, 101) + ' ' +
STUFF(right(CONVERT(varchar(50), @d, 109), 14), 9, 4, ' ')
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-02 : 10:36:45

quote:
There is no front end. These stored Procs are used to create reports in Excel.

The Excel is your front end. Why not use Excel to format the date & time ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-02 : 10:38:08

quote:
But only thing is wiht the time part with the Trainling space.
I need with the Trailing Zero.

Example ?

KH
[spoiler]Time is always against us[/spoiler]

avmreddy17
Posting Yak Master

180 Posts

Posted - 2007-12-02 : 11:03:07

Lets put it this way..I have to do the formatting in the SQL Stored Proc

Khtan..

DECLARE @d datetime
SET @d = '2007-12-01 09:31:52.160'

SELECT CONVERT(varchar(10), @d, 101) + ' ' +
STUFF(right(CONVERT(varchar(50), @d, 109), 14), 9, 4, ' ')

12/01/2007 9:31:52 AM

But I need

12/01/2007 09:31:52 AM

Thx
Venu

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-02 : 11:25:41

[code]SELECT CONVERT(varchar(10), @d, 101) + ' ' +
RIGHT('0' + CONVERT(varchar(2), DATEPART(hour, @d)), 2) + STUFF(right(CONVERT(varchar(30), @d, 109), 12), 7, 4, ' ')[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-02 : 11:26:20

Why don't you also explore using Excel to the formating ?

KH
[spoiler]Time is always against us[/spoiler]

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-03 : 02:38:20

SELECT CONVERT(VARCHAR(50),GETDATE(),121)
USE VALUES FROM 100 TO 121 AT THE PLACE OF 121 AND GET THE DESIRED OUTPUT.

Vinod
Even you learn 1%, Learn it with 100% confidence.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-03 : 08:42:58

quote:
Originally posted by sunsanvin

SELECT CONVERT(VARCHAR(50),GETDATE(),121)
USE VALUES FROM 100 TO 121 AT THE PLACE OF 121 AND GET THE DESIRED OUTPUT.

Vinod
Even you learn 1%, Learn it with 100% confidence.

That does not gives the format required by OP

KH
[spoiler]Time is always against us[/spoiler]

NeilG
Aged Yak Warrior

530 Posts

Posted - 2007-12-03 : 08:56:40

what about convert(varchar(10),getdate(),103)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources