converting birth date - 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

converting birth date

Author

Topic

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-06 : 16:17:44

HI,

I am trying to convert the birth dated from 19591229 to 12/29/1959 and using below code but its not giving me in that format its printing line 1959-12-29. how do I do this?

bdate=convert(date, p.date_of_birth,101)

Thanks!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-06 : 16:28:03

It's because you are converting to a date data type. You can use char/varchar instead, but this is really a presentation issue and should be handled by the application and not in T-SQL.

select convert(char(10), getdate(), 101)

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-06 : 16:36:28

Thanks Tara, but I need to include the "p.date_of_birth" into this code, how do I do that?
Pasi.

quote:
Originally posted by tkizer

It's because you are converting to a date data type. You can use char/varchar instead, but this is really a presentation issue and should be handled by the application and not in T-SQL.

select convert(char(10), getdate(), 101)

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-05-06 : 16:39:58

Replace the getdate() with p.date_of_birth

select convert(char(10), p.date_of_birth, 101)
from yourTableWithDateofBirthsEtc

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-06 : 17:44:16

Thanks I tried it and it does not work? I still get the same format "19591229 "
Pasi

quote:
Originally posted by James K

Replace the getdate() with p.date_of_birth
select convert(char(10), p.date_of_birth, 101)
from yourTableWithDateofBirthsEtc

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-06 : 17:48:50

I used this but it says "Conversion failed when converting the varchar value '/' to data type int."

bdate= DATEPART(m, p.date_of_birth) + '/' + DATEPART(d, p.date_of_birth)

]

quote:
Originally posted by James K

Replace the getdate() with p.date_of_birth
select convert(char(10), p.date_of_birth, 101)
from yourTableWithDateofBirthsEtc

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-05-06 : 17:55:44

First you should find out what the data type of the column p.date_of_birth is. Go to SQL Management Studio, object explorer, expand the nodes to Database -> Tables -> yourTableName -> columns and look up the date_of_birth column. If it is an integer, which I suspect it is, then you need to convert it date before you can do what I suggested. So it would be:

select convert(char(10), CAST(CAST(p.date_of_birth AS VARCHAR(10)) AS DATE), 101)
from yourTableWithDateofBirthsEtc p

If it is not integer, then what you need to do depends on what the data type is.

For this reason (and many other reasons), experts recommend that you store the data in datatypes appropriate for the data. In this instance, if date_of_birth were a column of type DATE, it would be easier to manipulate.

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-06 : 18:02:07

Thanks that fixed it. I looked up the table and this field is varchar (10). but your suggestion code works now.

Pasi.

quote:
Originally posted by James K

First you should find out what the data type of the column p.date_of_birth is. Go to SQL Management Studio, object explorer, expand the nodes to Database -> Tables -> yourTableName -> columns and look up the date_of_birth column. If it is an integer, which I suspect it is, then you need to convert it date before you can do what I suggested. So it would be:
select convert(char(10), CAST(CAST(p.date_of_birth AS VARCHAR(10)) AS DATE), 101)
from yourTableWithDateofBirthsEtc p
If it is not integer, then what you need to do depends on what the data type is.

For this reason (and many other reasons), experts recommend that you store the data in datatypes appropriate for the data. In this instance, if date_of_birth were a column of type DATE, it would be easier to manipulate.

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-06 : 18:07:14

James, so what does CAST function exactly do? Curious?
Thanks.!

quote:
Originally posted by James K

First you should find out what the data type of the column p.date_of_birth is. Go to SQL Management Studio, object explorer, expand the nodes to Database -> Tables -> yourTableName -> columns and look up the date_of_birth column. If it is an integer, which I suspect it is, then you need to convert it date before you can do what I suggested. So it would be:
select convert(char(10), CAST(CAST(p.date_of_birth AS VARCHAR(10)) AS DATE), 101)
from yourTableWithDateofBirthsEtc p
If it is not integer, then what you need to do depends on what the data type is.

For this reason (and many other reasons), experts recommend that you store the data in datatypes appropriate for the data. In this instance, if date_of_birth were a column of type DATE, it would be easier to manipulate.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-05-06 : 20:15:10

[code]so what does CAST function exactly do?[/code]Converts data in one data type to another data type.

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-07 : 11:04:05

Thanks again!!

quote:
Originally posted by James K

so what does CAST function exactly do?
Converts data in one data type to another data type.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources