dateadd Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

dateadd Function

Author

Topic

satheesh
Posting Yak Master

152 Posts

Posted - 2011-09-19 : 04:54:52

Dear All

I am calculating DOB(roughly) by

select dateadd(YEAR,-Traveller.Age ,Traveller.datecreated) AS DateOfBirth from traveller

I am getting result as '2003-06-17 16:24:00.693'

But i need to display the result in [DD-MM-YYYY] format ie. 17-06-2003

How to amend the dateadd function.Any help is appreciated.

Thanks
SG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 05:11:16

its a presentation issue and so can be best dealt with at your front end application using format functions.
If you really want to do it in sql use CONVERT(), but please keep in mind this is not at all recommended approach.

SELECT CONVERT(varchar(11),GETDATE(),103)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

satheesh
Posting Yak Master

152 Posts

Posted - 2011-09-19 : 05:37:30

Thanks Visakh

select CONVERT(VARCHAR(10), GETDATE(), 105) will retrive in
[DD-MM-YYYY] format [ie. 17-06-2003]

But i am calculating dob using age.So i would like to know how to merge below 2 function to get dob in [DD-MM-YYYY] format

1.select dateadd(YEAR,-Traveller.Age ,Traveller.datecreated)AS DateOfBirth [i am getting result as ['2003-06-17 16:24:00.693']

2.CONVERT(VARCHAR(10), GETDATE(), 105) will give [19-09-2011]
(result need to be in this format)

Thanks,
SG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 05:40:15

i gave an example.
i expected you to apply it to your main query

select convert(varchar(11),dateadd(YEAR,-Traveller.Age ,Traveller.datecreated),105) AS DateOfBirth from traveller

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

satheesh
Posting Yak Master

152 Posts

Posted - 2011-09-19 : 05:47:21

Many Thanks Visakh
Its working!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 05:52:08

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources