By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,364 Members | 1,268 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,364 IT Pros & Developers. It's quick & easy.

Suffix in dates

nitindel
P: 67
Hi All,
I want to add Suffix in dates like nd,st,rd,th to dates like 2,1,3,4 respectively.....

What is the function used in Sql Server and oracle both??

Thanks & Regards
Nitin Sharma
Software Engineer
.Net Technologies
Jun 26 '08 #1
Share this Question
Share on Google+
5 Replies


deepuv04
Expert 100+
P: 227
Hi,
here is the sample query (sql server) to give suffix to the date
sample code:
Expand|Select|Wrap|Line Numbers
  1.  
  2. DECLARE @Date datetime
  3. -- I am using @date variables, you can change it to column name if you are retrieveing data from table
  4. set @Date = Getdate()
  5.  
  6.   SELECT   CASE WHEN DAY(@Date) in (1,21,31) THEN convert(varchar,DAY(@Date)) + 'st'
  7.             WHEN DAY(@Date) IN (2,22) then convert(varchar,DAY(@Date)) + 'nd'
  8.             WHEN DAY(@Date) IN (3,23) then convert(varchar,DAY(@Date)) + 'rd' 
  9.             ELSE convert(varchar,DAY(@Date)) + 'th ' end +
  10.        DATENAME(month, @Date)  + ', ' + convert(varchar,year(@Date)) as CertificationDate
  11.  
thanks
Jun 26 '08 #2

nitindel
P: 67
Hi,
here is the sample query (sql server) to give suffix to the date
sample code:
Expand|Select|Wrap|Line Numbers
  1.  
  2. DECLARE @Date datetime
  3. -- I am using @date variables, you can change it to column name if you are retrieveing data from table
  4. set @Date = Getdate()
  5.  
  6.   SELECT   CASE WHEN DAY(@Date) in (1,21,31) THEN convert(varchar,DAY(@Date)) + 'st'
  7.             WHEN DAY(@Date) IN (2,22) then convert(varchar,DAY(@Date)) + 'nd'
  8.             WHEN DAY(@Date) IN (3,23) then convert(varchar,DAY(@Date)) + 'rd' 
  9.             ELSE convert(varchar,DAY(@Date)) + 'th ' end +
  10.        DATENAME(month, @Date)  + ', ' + convert(varchar,year(@Date)) as CertificationDate
  11.  
thanks


Hi Dear..
Thank you....

I am using the query sent by u...but m having problem in retrrieving the date as like ur query is returning...
I have a date field in the table with datetime as a datatype and i have replaced the Getdate() function with (Select date from tablename)..that is it....


Please tell what all i have to change....

My table name is XYZ
My field name is Date..

Thanks
Nitin
Jun 26 '08 #3

deepuv04
Expert 100+
P: 227
Hi Dear..
Thank you....

I am using the query sent by u...but m having problem in retrrieving the date as like ur query is returning...
I have a date field in the table with datetime as a datatype and i have replaced the Getdate() function with (Select date from tablename)..that is it....


Please tell what all i have to change....

My table name is XYZ
My field name is Date..

Thanks
Nitin
Hi,
what i mean to say is use the query as follows:

Expand|Select|Wrap|Line Numbers
  1. SELECT   CASE WHEN DAY(Date) in (1,21,31) THEN convert(varchar,DAY(Date)) + 'st'
  2.             WHEN DAY(Date) IN (2,22) then convert(varchar,DAY(Date)) + 'nd'
  3.             WHEN DAY(Date) IN (3,23) then convert(varchar,DAY(Date)) + 'rd'
  4.             ELSE convert(varchar,DAY(Date)) + 'th ' end +
  5.             DATENAME(month, Date)  + ', ' + convert(varchar,year(Date)) as CertificationDate
  6. FROM XYZ
  7.  
thanks
Jun 26 '08 #4

nitindel
P: 67
Hi,
what i mean to say is use the query as follows:

Expand|Select|Wrap|Line Numbers
  1. SELECT   CASE WHEN DAY(Date) in (1,21,31) THEN convert(varchar,DAY(Date)) + 'st'
  2.             WHEN DAY(Date) IN (2,22) then convert(varchar,DAY(Date)) + 'nd'
  3.             WHEN DAY(Date) IN (3,23) then convert(varchar,DAY(Date)) + 'rd'
  4.             ELSE convert(varchar,DAY(Date)) + 'th ' end +
  5.             DATENAME(month, Date)  + ', ' + convert(varchar,year(Date)) as CertificationDate
  6. FROM XYZ
  7.  
thanks


Thanks a lot..My dear..!! It worked..

Also..tell..What if it is not a date and a simple number field more than 30 or 31 ...

Waiting for the reply...!!

Thanks ,
Nitin Sharma
Jun 26 '08 #5

deepuv04
Expert 100+
P: 227
Thanks a lot..My dear..!! It worked..

Also..tell..What if it is not a date and a simple number field more than 30 or 31 ...

Waiting for the reply...!!

Thanks ,
Nitin Sharma
Hi,

sql server treats number 1 as 1900-01-01. (yyyy-mm-dd)

If you given any number then it starts counting from the date above, and convertrs it as another date. like

if you give the date as 0 then it takes 1900-01-01,
if the date is given as 32 then it will be 1900-02-02

for more details do as follows you will come to know exactly what happening

declare @date as datetime

set @date = 1
-- try with different numbers values

select @date as date


thanks
Jun 26 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.