| Author |
Topic |
|
omega1983
Starting Member
40 Posts |
 Posted - 2007-02-03 : 00:33:14
|
| I have a field called corebithd (date-time) which houses a birthday formatted such as 1958-01-05 or yyyy-mm-dd. a) I want to extract each portion of the corebirthd and put it in a set of three separate fields such as corebrthyr(char), corebrthmn(char) and corebirthday(char). b) I also want to display the corebrthmn and corebrthday as a 2 digit number, so the month of July should read 07, not 7. Is the CONVERT function the best way to handle this and how |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-02-03 : 02:26:09
|
(a) why do you want to do that ? It will be much easier to store it in datetime data type rather than 3 seperate int field(b) No. The best way is do it in your front end application. Alternatively if you want to do the formatting in SQL, use convert()
KH |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-03 : 03:55:34
|
"why do you want to do that"We did that on one app. Client wanted birthdays that were incomplete (i.e. relatives couldn't remember exactly) and to do fussy fuzzy searching on parts of the date - so typing in 23-Nov-1922 would also check a few adjacent years and so on. It was easier with three fields (but we used INT rather than CHAR!!)"so the month of July should read 07"CONVERT won't (that I know of) add leading zeros. You can do that with Convert and some additional string handling though.Kristen |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-02-03 : 03:59:35
|
convert() will if the DOB is stored in datatime datatype.
KH |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-03 : 05:02:29
|
| "convert() will if the DOB is stored in datatime datatype"Indeed. And good point! |
|
|
|
omega1983
Starting Member
40 Posts |
Posted - 2007-02-03 : 13:52:27
|
| I do not have a choice in the matter. The three fields are already set up to take in the corebirthd segmented so If you have a code I can use I would appreciated it. TO recap, corebirthd is a date-time data type, the 3 ote fields are char type |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-02-03 : 19:03:21
|
[code]corebrthyr = left(convert(varchar(10), corebirthd, 121), 4)corebrthmn = substring(convert(varchar(10), corebirthd, 121), 5,2)corebirthday = right(convert(varchar(10), corebirthd, 121), 2)[/code]
KH |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-02-03 : 19:56:00
|
quote:
Originally posted by Kristen...fussy searching...
I think that's what my kid does when she can't find her binky...
www.elsasoft.org |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-04 : 04:17:15
|
Is "binky" dyslexic for something else then?!  |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-02-04 : 04:35:40
|
| select datepart(year, corebirthd), datepart(month, corebirthd), datepart(day, corebirthd)Peter LarssonHelsingborg, Sweden |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-02-04 : 15:25:30
|
quote:
Originally posted by omega1983
I do not have a choice in the matter. The three fields are already set up to take in the corebirthd segmented so If you have a code I can use I would appreciated it. TO recap, corebirthd is a date-time data type, the 3 ote fields are char type
Yet another horrible database design decision resulting in more work for everyone and less accurate data ... why do people work so hard to avoid doing things the easy and correct way?- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-02-04 : 16:46:28
|
| "I do not have a choice..."I don't see the value of these columns. I doubt if they would be any use as indexes for lookups on a partial birthdays compared to just scanning a datetime column.If they are just there for reformatting for display, thats just another bad idea.CODO ERGO SUM |
|
|
|
|
getdate and CONVERT to manipulate a date