CODEWITHSUNDEEP
sql-serverdatetimeselect
DareDevil
DareDevil

Reputation: 5349

Replace YEAR part with custom year in SQL Server Select statement

I am working with SQL Server and a table containing DOB [Date Of Birth] column.

I have saved a value in table against that column, that is = 'May 5 1988 12:00AM', but I need 'May 5 1900 12:00AM' when I select the column.

I have this basic query:

SELECT CAST(dbo.contact.dob AS VARCHAR) AS DOBFROM dbo.contact

The result is May 5 1988 12:00AM

Any help?

Upvotes: 1

Views: 2431

Answers (4)

DiegoCoderPlus
DiegoCoderPlus

Reputation: 770

I hope I can help you out, i think what you need is: 

 SELECT DATEADD(YEAR, (1900-(DATEPART(YEAR, dbo.contact.dob))), dbo.contact.dob ) AS DOB FROM dbo.contact

DATEADD and DATEPART will do the trick

Upvotes: 1

Mahesh
Mahesh

Reputation: 8892

If you are going to replace the custom year then this might help you. Here GETDATE() is your datecolumn.

DECLARE @CustomYear = '1900'
SELECT REPLACE(CAST(GETDATE() AS VARCHAR(MAX)),DATEPART(YEAR,GETDATE()),@CustomYear)

This gives me output as,

Mar 17 1900 4:12AM

Upvotes: 1

dean
dean

Reputation: 10098

A simple date math:

select dateadd(year, -datediff(year, '19000101', getdate()), getdate())

(where getdate() stands for your table's column).

You might have a problem with leap years, though.

Upvotes: 1

janilemy
janilemy

Reputation: 565

Try to add 2 years like 

SELECT CAST(DATEADD(year,2,dbo.contact.dob) AS VARCHAR) AS DOB
FROM dbo.contact

This will add you years so you will get for May 5 1988 12:00AM -> added 2 years like May 5 1990 12:00AM

Upvotes: 0

Related Questions