Convert DD-Mon-YYY to DD/MM/YYYY

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

SQL Server 2008 Forums

Transact-SQL (2008)

Convert DD-Mon-YYY to DD/MM/YYYY

Author

Topic

arthiasha
Starting Member

40 Posts

Posted - 2012-10-25 : 01:16:41

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

40 Posts

Posted - 2012-10-25 : 01:31:27

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?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-10-25 : 01:40:05

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
[spoiler]Time is always against us[/spoiler]

arthiasha
Starting Member

40 Posts

Posted - 2012-10-25 : 05:51:28

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-25 : 06:50:40

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-11-02 : 04:34:37

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

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2012-11-11 : 19:19:14

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources