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:
[code=sql]

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 CertificationDa te
[/code]

thanks

[nitindel]

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

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 CertificationDa te
[/code]

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)..tha t 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)..tha t 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:

[code=sql]
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 CertificationDa te
FROM XYZ
[/code]
thanks

[nitindel]

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

[code=sql]
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 CertificationDa te
FROM XYZ
[/code]
thanks

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

Also..tell..Wha t 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..Wha t 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