SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Convert DD-Mon-YYY to DD/MM/YYYY
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

arthiasha
Starting Member

India
40 Posts

Posted - 10/25/2012 :  01:16:41  Show Profile  Reply with Quote
Hi experts,
I have my stored procedure here and the problem is i have 2 tables temp and employee in which the columns datatype differs
temp : dt_of_birth [varchar](15),dt_of_joining [varchar](15)
employee : dt_of_birth datetime,dt_of_joining datetime.


SET NOCOUNT ON
DECLARE @employee_id varchar(7),@emp_id int,@first_name char(50),
@middle_name char(50),@last_name char(50),
@title char(5),@sex char(1),
@dt_of_birth datetime,@dt_of_joining datetime,
@father_name char(40), @perm_address_line_1 char(200),
@perm_address_line_2 char(200), @per_city char(20),
@perm_state char(12), @perm_pincode int,
@perm_phone_landline char(15),@perm_phone_mobile char(15),
@present_address_line_1 char(200), @present_address_line_2 char(200),
@present_city char(20), @present_state char(12),
@present_pincode int, @marital_status char(1),
@dt_of_marriage datetime, @qualification char(10),
@blood_group char(3),@email_id char(30),
@workex_current smallint, @workex_past smallint,
@emergency_contact_name char(50),@emergency_contact_telno char(15),
@dept_code char(7),@division_code char(7),@branch_code char(5),
@designation char(10),@from_date datetime,
@to_date datetime, @grade_code char(5), @manager_id char(5)


DECLARE cur_split CURSOR FOR

SELECT * FROM temp

OPEN cur_split
FETCH NEXT from cur_split into @employee_id,@emp_id,@first_name,@middle_name,
@last_name,@title,@sex,@dt_of_birth,
@dt_of_joining,@father_name,@perm_address_line_1,
@perm_address_line_2,@per_city,
@perm_state,@perm_pincode,@perm_phone_landline,
@perm_phone_mobile,@present_address_line_1,
@present_address_line_2,@present_city,@present_state,
@present_pincode,@marital_status,
@dt_of_marriage,@qualification,@blood_group,
@email_id,@workex_current,@workex_past,
@emergency_contact_name,@emergency_contact_telno,
@dept_code,@division_code,@branch_code,
@designation,@from_date,@to_date,@grade_code,@manager_id

WHILE @@FETCH_STATUS = 0
BEGIN

SET IDENTITY_INSERT employee ON
insert into employee(employee_id,emp_id,first_name,middle_name,last_name,
title,sex,dt_of_birth,dt_of_joining,father_name,
perm_address_line_1,perm_address_line_2,perm_city,
perm_state,perm_pincode,perm_phone_landline,
perm_phone_mobile,present_address_line_1,present_address_line_2,
present_city,present_state,present_pincode,marital_status,
dt_of_marriage,qualification,blood_group,email_id,
workex_current,workex_past,emergency_contact_name,emergency_contact_telnon)
values(@employee_id,@emp_id,@first_name,@middle_name,@last_name,
@title,@sex,@dt_of_birth,@dt_of_joining,@father_name,
@perm_address_line_1,@perm_address_line_2,@per_city,
@perm_state,@perm_pincode,@perm_phone_landline,
@perm_phone_mobile,@present_address_line_1,
@present_address_line_2,@present_city,@present_state,
@present_pincode,@marital_status,
@dt_of_marriage,@qualification,@blood_group,
@email_id,@workex_current,@workex_past,
@emergency_contact_name,@emergency_contact_telno)

SET IDENTITY_INSERT employee OFF


insert into employee_details (employee_id,dept_code,division_code,branch_code,
designation,from_date,to_date,grade_code,manager_id)
values (@employee_id,@dept_code,@division_code,@branch_code,@designation,
@from_date,@to_date,@grade_code,@manager_id)

FETCH NEXT from cur_split into @employee_id,@emp_id,@first_name,@middle_name,
@last_name,@title,@sex,@dt_of_birth,
@dt_of_joining,@father_name,@perm_address_line_1,
@perm_address_line_2,@per_city,
@perm_state,@perm_pincode,@perm_phone_landline,
@perm_phone_mobile,@present_address_line_1,
@present_address_line_2,@present_city,@present_state,
@present_pincode,@marital_status,
@dt_of_marriage,@qualification,@blood_group,
@email_id,@workex_current,@workex_past,
@emergency_contact_name,@emergency_contact_telno,
@dept_code,@division_code,@branch_code,
@designation,@from_date,@to_date,@grade_code,@manager_id

END

CLOSE cur_split

deallocate cur_split






In my procedure i fetch the data from temp table and store it in employees and employee_details table according to the columns.
The date format differs in temp and employees table and i need to do a conversion to get it as a string as DD-Mon-YYYY and convert it to DD/MM/YYYY. How do i include this conversion in my procedure?

arthiasha
Starting Member

India
40 Posts

Posted - 10/25/2012 :  01:31:27  Show Profile  Reply with Quote
I can do the conversion like

select convert(varchar,dt_of_birth,103),
convert(varchar,dt_of_joining,103)
from temp

Can u please tell me how to include this in my procedure and store the output of this query into my employees table?
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17431 Posts

Posted - 10/25/2012 :  01:40:05  Show Profile  Reply with Quote
Don't use cursor. Set based is the way

insert into employee_details (employee_id,dept_code,division_code,branch_code, designation,from_date,to_date,grade_code,manager_id)
select	employee_id, dept_code, division_code, branch_code, designation, convert(datetime, from_date, 103), convert(datetime, to_date, 103), grade_code, manager_id
from	[temp]



KH
Time is always against us

Go to Top of Page

arthiasha
Starting Member

India
40 Posts

Posted - 10/25/2012 :  05:51:28  Show Profile  Reply with Quote
Thank u so much...
It worked out..
but now the date which i give as '10-Aug-2010' is converted and stored in the table as '2010-08-10'
but i want the date to be stored as '10/08/2010'
I tried with your it does the conversion but i wanted in this format...
Please help me
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 10/25/2012 :  06:50:40  Show Profile  Reply with Quote
quote:
Originally posted by arthiasha

Thank u so much...
It worked out..
but now the date which i give as '10-Aug-2010' is converted and stored in the table as '2010-08-10'
but i want the date to be stored as '10/08/2010'
I tried with your it does the conversion but i wanted in this format...
Please help me



Check this link...

http://www.sql-server-helper.com/sql-server-2008/sql-server-2008-date-format.aspx

--
Chandu
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 11/02/2012 :  04:34:37  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Always use proper DATETIME datatype to store datetime values and do the formation at the front end application. If there is no change to use datetime datatype, store them in unambigious date formats http://beyondrelational.com/modules/2/blogs/70/posts/10898/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 11/11/2012 :  19:19:14  Show Profile  Reply with Quote
quote:
Originally posted by arthiasha

Thank u so much...
It worked out..
but now the date which i give as '10-Aug-2010' is converted and stored in the table as '2010-08-10'
but i want the date to be stored as '10/08/2010'
I tried with your it does the conversion but i wanted in this format...
Please help me



I'll second what Madhivanan posted. Don't ever store formatted dates. You WILL end up hating yourself for doing it in the future.


--Jeff Moden
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.08 seconds. Powered By: Snitz Forums 2000