Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Date Format Expand / Collapse
Author
Message
Posted Wednesday, October 6, 2010 4:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 5:08 AM
Points: 7, Visits: 60
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
Post #999100
Posted Wednesday, October 6, 2010 5:27 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:15 AM
Points: 2,372, Visits: 7,562
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]




Not a DBA, just trying to learn

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #999148
Posted Wednesday, October 6, 2010 5:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:25 AM
Points: 2,658, Visits: 4,733
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/
Post #999164
Posted Wednesday, October 6, 2010 5:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:15 AM
Points: 2,372, Visits: 7,562
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
*/




Not a DBA, just trying to learn

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #999171
Posted Wednesday, October 6, 2010 6:00 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:25 AM
Points: 2,658, Visits: 4,733
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/
Post #999181
Posted Wednesday, October 6, 2010 6:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:15 AM
Points: 2,372, Visits: 7,562
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.



Not a DBA, just trying to learn

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #999188
Posted Wednesday, October 6, 2010 6:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:19 AM
Points: 6,721, Visits: 13,832
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
Exploring Recursive CTEs by Example Dwain Camps
Post #999189
Posted Wednesday, October 6, 2010 6:42 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, December 26, 2012 11:03 PM
Points: 586, Visits: 2,195
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
Post #999216
Posted Wednesday, October 6, 2010 10:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 5:08 AM
Points: 7, Visits: 60

sorry Chris Morris-439714, it's PM
Post #999565
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse