Date Format

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Date Format

11 posts, Page 1 of 2

1 2 »»

Date Format

Rate Topic

Display Mode

Topic Options

Author

Message

Minnu

Posted Wednesday, December 05, 2012 5:18 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 122, Visits: 366

Hi Team,

Am using below code to display name and "Date of birth"
but Field DOB is not coming in Date format.

select @FinalString=(SELECT RTRIM(+ISNULL('Name ','')+' : ' +ISNULL(+CAST(NAME AS VARCHAR(100))+' ','') +ISNULL(' DOB :'+CAST(DOB AS VARCHAR(100))+' ',''))
FROM ETCOL i want date format in below format.

rtrim(convert(varchar, DOB,107)) + ')'

Can u please help me .....

Post #1392931

anthony.green

Posted Wednesday, December 05, 2012 5:29 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Can you provide some sample data of the format that the DOB is and how it should be formatted

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1392935

Minnu

Posted Wednesday, December 05, 2012 5:32 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 122, Visits: 366

Date format in DOB Column is : '2012-09-30 00:00:00.000'

i want date format in : Sep 30, 2012

Post #1392936

anthony.green

Posted Wednesday, December 05, 2012 5:35 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Well that converts perfectly.

Can you provide the DDL and sample data for table ETCOL along with your full query?

Post #1392938

Minnu

Posted Wednesday, December 05, 2012 6:04 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 122, Visits: 366

BEGIN
select @FinalString =(SELECT RTRIM(+ISNULL('Name','')+' : ' +ISNULL(+CAST(Name AS VARCHAR(100))+' ',''))
FROM ETCOL WHERE RIGHT_TEXT ='Name') +CHAR(10)
END

ELSE
BEGIN
select @FinalString=(SELECT RTRIM(+ISNULL('Name','')+' : ' +ISNULL(+CAST(Name AS VARCHAR(100))+' ','') +ISNULL(' DOB :'+CAST(DOB AS VARCHAR(100))+' ',''))
FROM ETCOL WHERE RIGHT_TEXT ='Name') +CHAR(10)
END

----------

If the DOB is not null, then i want the date format like : Sep 30, 2012

Post #1392951

anthony.green

Posted Wednesday, December 05, 2012 6:05 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Please post the create table definition along with data for the table so what we can build up the string for you.

Post #1392953

Minnu

Posted Wednesday, December 05, 2012 6:08 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 122, Visits: 366

CREATE TABLE ETCOL (NAME VARCHAR (50), DOB DATETIME)

Post #1392958

anthony.green

Posted Wednesday, December 05, 2012 6:11 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

And now some data?

Post #1392959

Minnu

Posted Wednesday, December 05, 2012 6:27 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 122, Visits: 366

Hi Team,

Table and data is normat,

in my query am checking whether it is null or not null and
am not converting to normal date format.

can u please convert it to normal date if it is not null.

Please help me.....

Post #1392969

DiverKas

Posted Wednesday, December 05, 2012 6:29 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:24 AM
Points: 237, Visits: 413

Minnu (12/5/2012)

If you want it formated like

 rtrim(convert(varchar, DOB,107))

then use it in the select statement?

 select @FinalString=(SELECT RTRIM(+ISNULL('Name ','')+'  : ' + 
ISNULL(+CAST(NAME AS VARCHAR(100))+' ','') + 
ISNULL(' DOB :'+rtrim(convert(varchar(10), DOB,107)) +' ','')) FROM ETCOL

I prefer to be explicit with my CONVERT(VARCHAR(12), DOB,107), since you are going to get a 30 character varchar variable by default, instead of the 12 character that you need.

Post #1392971

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions