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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 getdate and CONVERT to manipulate a date
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

omega1983
Starting Member

40 Posts

Posted - 02/03/2007 :  00:33:14  Show Profile  Reply with Quote
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)

Singapore
17689 Posts

Posted - 02/03/2007 :  02:26:09  Show Profile  Reply with Quote
(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


Edited by - khtan on 02/03/2007 02:49:53
Go to Top of Page

Kristen
Test

United Kingdom
22859 Posts

Posted - 02/03/2007 :  03:55:34  Show Profile  Reply with Quote
"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

Edited by - Kristen on 02/04/2007 04:18:08
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 02/03/2007 :  03:59:35  Show Profile  Reply with Quote
convert() will if the DOB is stored in datatime datatype.


KH

Go to Top of Page

Kristen
Test

United Kingdom
22859 Posts

Posted - 02/03/2007 :  05:02:29  Show Profile  Reply with Quote
"convert() will if the DOB is stored in datatime datatype"

Indeed. And good point!
Go to Top of Page

omega1983
Starting Member

40 Posts

Posted - 02/03/2007 :  13:52:27  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 02/03/2007 :  19:03:21  Show Profile  Reply with Quote

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)



KH

Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 02/03/2007 :  19:56:00  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22859 Posts

Posted - 02/04/2007 :  04:17:15  Show Profile  Reply with Quote
Is "binky" dyslexic for something else then?!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 02/04/2007 :  04:35:40  Show Profile  Visit SwePeso's Homepage  Reply with Quote
select datepart(year, corebirthd), datepart(month, corebirthd), datepart(day, corebirthd)


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 02/04/2007 :  15:25:30  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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?

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 02/04/2007 :  15:32:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Because they haven't seen this function?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/04/2007 :  16:46:28  Show Profile  Reply with Quote
"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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000