Suffix in dates

[nitindel]

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

[deepuv04]

Hi,
here is the sample query (sql server) to give suffix to the date
sample code:

 
DECLARE @Date datetime
-- I am using @date variables, you can change it to column name if you are retrieveing data from table
set @Date = Getdate()
 
  SELECT   CASE WHEN DAY(@Date) in (1,21,31) THEN convert(varchar,DAY(@Date)) + 'st'
            WHEN DAY(@Date) IN (2,22) then convert(varchar,DAY(@Date)) + 'nd'
            WHEN DAY(@Date) IN (3,23) then convert(varchar,DAY(@Date)) + 'rd' 
            ELSE convert(varchar,DAY(@Date)) + 'th ' end +
       DATENAME(month, @Date)  + ', ' + convert(varchar,year(@Date)) as CertificationDate
 

thanks

[nitindel]

Hi,
here is the sample query (sql server) to give suffix to the date
sample code:

 
DECLARE @Date datetime
-- I am using @date variables, you can change it to column name if you are retrieveing data from table
set @Date = Getdate()
 
  SELECT   CASE WHEN DAY(@Date) in (1,21,31) THEN convert(varchar,DAY(@Date)) + 'st'
            WHEN DAY(@Date) IN (2,22) then convert(varchar,DAY(@Date)) + 'nd'
            WHEN DAY(@Date) IN (3,23) then convert(varchar,DAY(@Date)) + 'rd' 
            ELSE convert(varchar,DAY(@Date)) + 'th ' end +
       DATENAME(month, @Date)  + ', ' + convert(varchar,year(@Date)) as CertificationDate
 

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

[deepuv04]

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:

SELECT   CASE WHEN DAY(Date) in (1,21,31) THEN convert(varchar,DAY(Date)) + 'st'
            WHEN DAY(Date) IN (2,22) then convert(varchar,DAY(Date)) + 'nd'
            WHEN DAY(Date) IN (3,23) then convert(varchar,DAY(Date)) + 'rd'
            ELSE convert(varchar,DAY(Date)) + 'th ' end +
            DATENAME(month, Date)  + ', ' + convert(varchar,year(Date)) as CertificationDate
FROM XYZ
 

thanks

[nitindel]

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

SELECT   CASE WHEN DAY(Date) in (1,21,31) THEN convert(varchar,DAY(Date)) + 'st'
            WHEN DAY(Date) IN (2,22) then convert(varchar,DAY(Date)) + 'nd'
            WHEN DAY(Date) IN (3,23) then convert(varchar,DAY(Date)) + 'rd'
            ELSE convert(varchar,DAY(Date)) + 'th ' end +
            DATENAME(month, Date)  + ', ' + convert(varchar,year(Date)) as CertificationDate
FROM XYZ
 

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

[deepuv04]

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