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 12»»

Date Format Expand / Collapse
Author
Message
Posted Wednesday, December 5, 2012 5:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 10:09 AM
Points: 227, Visits: 713
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
Posted Wednesday, December 5, 2012 5:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:20 AM
Points: 5,218, Visits: 5,067
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
Posted Wednesday, December 5, 2012 5:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 10:09 AM
Points: 227, Visits: 713
Date format in DOB Column is : '2012-09-30 00:00:00.000'

i want date format in : Sep 30, 2012
Post #1392936
Posted Wednesday, December 5, 2012 5:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:20 AM
Points: 5,218, Visits: 5,067
Well that converts perfectly.

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




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 #1392938
Posted Wednesday, December 5, 2012 6:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 10:09 AM
Points: 227, Visits: 713
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
Posted Wednesday, December 5, 2012 6:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:20 AM
Points: 5,218, Visits: 5,067
Please post the create table definition along with data for the table so what we can build up the string for you.



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 #1392953
Posted Wednesday, December 5, 2012 6:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 10:09 AM
Points: 227, Visits: 713
CREATE TABLE ETCOL (NAME VARCHAR (50), DOB DATETIME)
Post #1392958
Posted Wednesday, December 5, 2012 6:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:20 AM
Points: 5,218, Visits: 5,067
And now some data?



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 #1392959
Posted Wednesday, December 5, 2012 6:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 10:09 AM
Points: 227, Visits: 713
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
Posted Wednesday, December 5, 2012 6:29 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:28 AM
Points: 249, Visits: 460
Minnu (12/5/2012)
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 .....


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 »

Add to briefcase 12»»

Permissions Expand / Collapse