CODEWITHSUNDEEP
sqlsql-server-2005
Sheetal Inani
Sheetal Inani

Reputation: 128

converting date data type into varchar

I have some dates fields in table. These columns contain dates in the following format:

mmddyy

For example:

31/12/2010 00:00:00:0000

I need to import these values into a table which is set to varchar and numeric and formats dates like this:

monthName varchar
Year numeric(4,0)

currently I'm using

INSERT INTO [School].[dbo].[TeacherAttendenceDet] 
            ([TeacherCode], 
             [MonthName], 
             [Year]) 
(SELECT MAX(employeecode), 
        Datename(MONTH, dateofjoining) AS MONTH, 
        Datepart(YEAR, dateofjoining)  AS DATE 
 FROM   employeedet 
 GROUP  BY dateofjoining)

but datename() gives result in date format.. I have to save it in varchar format

How can I do this? this is employeemast table:

EmployeeCode    numeric(5, 0)   
PayScaleCode    numeric(7, 0)   
DesignationCode varchar(50) 
CityCode    numeric(5, 0)   
EmployeeName    varchar(50) 
FatherName  varchar(50) 
BirthDate   varchar(50) 
DateOfJoining   varchar(50) 
Address varchar(150)

this is TeacherAttendenceDet table

TeacherCode numeric(5, 0)   Unchecked
Year    numeric(4, 0)   Unchecked
MonthName   varchar(12) Unchecked

i have to insert in teacherattendencedet table the monthname and year from employeemast

Upvotes: 1

Views: 1918

Answers (1)

marc_s
marc_s

Reputation: 755157

If I were in your position, I would do the following:

  • update the EmployeeMast table by adding a real DATETIME column:

    ALTER TABLE dbo.EmployeeMast
  ADD JoinDate DATETIME

  • then convert all those entries in datejoining into the new datetime column:

     UPDATE dbo.EmployeeMast
 SET JoinDate = CONVERT(DATETIME, SUBSTRING(datejoining, 1, 10), 103)

  • then drop that old column 

      ALTER TABLE dbo.EmployeeMast DROP COLUMN datejoining

Now, once you have a DATETIME column, you can start using the date functions on that!

Now, your INSERT statement should work - since it does get its values from a DATETIME column, it will be able to compute DATENAME() and DATEPART() !

If you want to have the year (as numeric) and the month (as string) for the join date for each entry, I would recommend adding two computed fields to your table - something like:

alter table dbo.EmployeeMast
  add JoinYear AS YEAR(JoinDate) PERSISTED

alter table dbo.EmployeeMast
  add JoinMonth AS DATENAME(MONTH, JoinDate)

With this, you now have two additional columns - JoinYear as a numeric, JoinMonth as a string - that will always reflect the year and month someone joined - based on JoinDate.

They're computed - you don't need to insert those - they'll be computed from the JoinDate automagically and will always be available to you. No conversion, no messy inserts - just insert your row with the JoinDate and you'll have the year and month available right away.

Upvotes: 2

Related Questions