Date Format

Question

Post reply

Date Format

LIYA

SSC Rookie

Points: 39
More actions
October 6, 2010 at 4:05 am

#224725

Hi All,
i have a table with following structure
CREATE TABLE [dbo].[DateFormat](
[WDate] [smalldatetime] NULL
) ON [PRIMARY]
insert into DateFormat
select '2010-06-18 07:33:17' union all
select '2010-06-18 07:40:18' union all
select '2010-06-18 18:40:10' union all
select '2010-06-18 14:45:17' union all
i need the result as follows
Wdate
18/06/2010 07:33:17 AM
18/06/2010 07:40:18 AM
18/06/2010 06:40:10 PM
18/06/2010 02:45:17 PM
I tried the following code
SELECT CONVERT(varchar(12), WDate, 103) + ' ' + SUBSTRING(CONVERT(varchar(28), WDate, 9), 13, 15) AS Date
FROM Dateformat
i got the result as follows . but it's having the mi sec part like
18/06/2010 07:33:17:000AM

Viewing 9 posts - 1 through 8 (of 8 total)

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

I'm sure there must be a better way to do this, but try this: -

SELECT CONVERT(VARCHAR, wdate, 103) + ' ' + Ltrim(

Substring(CONVERT(VARCHAR, wdate, 100), 13, Len(wdate))) AS DATE

FROM [Dateformat]

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 2

Removed as the solution was incorrect..

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

Kingston Dhasian (10/6/2010)
Normally formatting such as these is done in the front end. Anyways this should help you out
SELECT CONVERT(varchar(12), WDate, 103) + ' ' + CONVERT(varchar(28), WDate, 8) + ' ' +
CASE WHEN DATEPART( HOUR, WDATE ) < 12 THEN 'AM' ELSE 'PM' END,
CONVERT(varchar(28), WDate, 9)
FROM Dateformat

That wouldn't work Kingston, any PM time will show up as a 24 hour clock but with PM at the end, which is redundant.

E.G.

DECLARE @Var AS SMALLDATETIME

SET @Var = '2010-06-18 17:33:17'

SELECT CONVERT(varchar(12), @Var, 103) + ' ' + CONVERT(varchar(28), @Var, 8) + ' ' +

CASE WHEN DATEPART( HOUR, @Var ) < 12 THEN 'AM' ELSE 'PM' END,

CONVERT(varchar(28), @Var, 9)

/*Returns

-------------------------------------------- ----------------------------

18/06/2010 17:33:00 PM Jun 18 2010 5:33:00:000PM

*/

Whereas: -

DECLARE @Var AS SMALLDATETIME

SET @Var = '2010-06-18 17:33:17'

SELECT CONVERT(VARCHAR, @Var, 103) + ' ' + Ltrim(

Substring(CONVERT(VARCHAR, @Var, 100), 13, Len(@Var))),

CONVERT(VARCHAR(28), @Var, 9)

/*Returns

------------------------------------------------------------- ----------------------------

18/06/2010 5:33PM Jun 18 2010 5:33:00:000PM

*/

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 4

skcadavre (10/6/2010)
Kingston Dhasian (10/6/2010)
Normally formatting such as these is done in the front end. Anyways this should help you out
SELECT CONVERT(varchar(12), WDate, 103) + ' ' + CONVERT(varchar(28), WDate, 8) + ' ' +
CASE WHEN DATEPART( HOUR, WDATE ) < 12 THEN 'AM' ELSE 'PM' END,
CONVERT(varchar(28), WDate, 9)
FROM Dateformat
That wouldn't work Kingston, any PM time will show up as a 24 hour clock but with PM at the end, which is redundant.
E.G.
DECLARE @Var AS SMALLDATETIME
SET @Var = '2010-06-18 17:33:17'
SELECT CONVERT(varchar(12), @Var, 103) + ' ' + CONVERT(varchar(28), @Var, 8) + ' ' +
CASE WHEN DATEPART( HOUR, @Var ) < 12 THEN 'AM' ELSE 'PM' END,
CONVERT(varchar(28), @Var, 9)
/*Returns
-------------------------------------------- ----------------------------
18/06/2010 17:33:00 PM Jun 18 2010 5:33:00:000PM
*/
Whereas: -
DECLARE @Var AS SMALLDATETIME
SET @Var = '2010-06-18 17:33:17'
SELECT CONVERT(VARCHAR, @Var, 103) + ' ' + Ltrim(
Substring(CONVERT(VARCHAR, @Var, 100), 13, Len(@Var))),
CONVERT(VARCHAR(28), @Var, 9)
/*Returns
------------------------------------------------------------- ----------------------------
18/06/2010 5:33PM Jun 18 2010 5:33:00:000PM
*/

Oh. You are right. Your solution is the correct one then.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

Kingston Dhasian (10/6/2010)
Oh. You are right. Your solution is the correct one then.

Still, it's a presentation layer task rather than a DB task, as you pointed out.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

LIYA (10/6/2010)
insert into DateFormat
select '2010-06-18 07:33:17' union all
select '2010-06-18 07:40:18' union all
select '2010-06-18 18:40:10' union all
select '2010-06-18 14:45:17' union all
i need the result as follows
Wdate
18/06/2010 07:33:17 AM
18/06/2010 07:40:18 AM
18/06/2010 06:40:10 AM
18/06/2010 02:45:17 AM

Can you confirm that you do actually want the last two rows to display "AM" and not "PM"?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

LIYA (10/6/2010)
Hi All,
i have a table with following structure
CREATE TABLE [dbo].[DateFormat](
[WDate] [smalldatetime] NULL
) ON [PRIMARY]
insert into DateFormat
select '2010-06-18 07:33:17' union all
select '2010-06-18 07:40:18' union all
select '2010-06-18 18:40:10' union all
select '2010-06-18 14:45:17' union all
i need the result as follows
Wdate
18/06/2010 07:33:17 AM
18/06/2010 07:40:18 AM
18/06/2010 06:40:10 AM
18/06/2010 02:45:17 AM
I tried the following code
SELECT CONVERT(varchar(12), WDate, 103) + ' ' + SUBSTRING(CONVERT(varchar(28), WDate, 9), 13, 15) AS Date
FROM Dateformat
i got the result as follows . but it's having the mi sec part like
18/06/2010 07:33:17:000AM

Hi

One thing dont forget that smalldatetime will not give you seconds so i have kept as datetime

DECLARE @Var AS DATETIME

SET @Var = '2010-06-18 17:33:23'

Select Convert(varchar,@Var,103)+' '+Convert(varchar,Case when DATEPART( HOUR, @Var )>12 then DATEPART( HOUR, @Var )-12 else DATEPART( HOUR, @Var ) end )+':'+Convert(varchar,DATEPART( MINUTE, @Var ))+':'+Convert(varchar(5),DATEPART( SS, @Var ))

+ ' ' +CASE WHEN DATEPART( HOUR, @Var) > 12 THEN 'PM' ELSE 'AM' END

Thanks

Parthi

Thanks
Parthi

LIYA SSC Rookie Points: 39 More actions · Answer 8

LIYA

SSC Rookie

Points: 39

October 6, 2010 at 10:10 am

#1232761

sorry Chris Morris-439714, it's PM