how to convert a datetime to varchar for comparing a field

Question

I am importing an Excel file into the SQL server table.  One of the fields is called DOB and it displays for example as 04141963.

However, when the import is completed in the SQL Server table, the format is datetime.  I am having trouble to compare the datetime with a varchar field from another table.

I am trying to change the DOB from the Excel file for the entire column by:

Got to Data -> Text to Column Option.  Select Delimited option in the first screen and than follow next steps.  Don't change anything in next steps.  GO to next and last steps, change the COlumn Data to Text, and Finish.

This won't work.

Could you please help to show me how to change the Excel file for a field in datetime to a varchar data.

Thank you very much for your help.

Sally

Answer 1

Hi Sally, if you want to convert the value in the comparison itself, you can use cast. Cast( DOB, varchar(20)) will return the dob as a varchar. Hope that helps! 

Answer 2

Conversion from datetime to string date formats and vice versa:

http://www.sqlusa.com/bestpractices/datetimeconversion/

Your best option is to convert from string to datetime/date and then compare.

Use CONVERT/TRY_CONVERT instead of the limited CAST.

Use ISDATE to validate a string date (if not using TRY_CONVERT).

---

Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

Answer 3

>>>Could you please help to show me how to change the Excel file for a field in datetime to a varchar data.

http://www.digdb.com/excel_add_ins/convert_data_type_text_general/

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 4

what date value does 04141963 represent?

14th Apr 1963?

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Answer 5

date value 04141963 represents:

4/14/1963

Thanks Kalman.

Sally

Answer 6

--step one, convert the int to a varchar CONVERT(varchar,04141963)
--step two, standardize the string RIGHT('00'+CONVERT(varchar,04141963),8) (replace any missing 0s from the front).
--step three, reformat the string. Put the year first, followed by a dash, followed by the month, another dash and finally the day.
--step four, convert the properly handled string to a date.

SELECT CONVERT(DATE,RIGHT(RIGHT('00'+CONVERT(varchar,04141963),8),4)+'-'+LEFT(RIGHT('00'+CONVERT(varchar,04141963),8),2)+'-'+SUBSTRING(RIGHT('00'+CONVERT(varchar,04141963),8),3,2))

If your string comes over like this, you can convert it to a date object in SQL Server (or a datetime, if you have an older version) like this.

Answer 7

Now I need to find out which approach is workable for this issue:

I expected the dob is a varchar data as it displays in Excel as 8-digit field like for this example:

(everything in this dob field displays 8-digit)

04141963

After the import, dob is a datetime field which shows as 4/14/1963.

I need to compare the dob field with another field called birth_date which is defined as varchar(8).

So my problem is to compare the dob with the birth_date field.

Which one is the better approach?

Should I convert dob to a varchar first then compare with the birth_date?

or

Should I convert the birth_date to a datetime and compare with the dob?

I am having problem to script this comparion.

Any advice and suggestion would be greatly appreciated.

Thanks,

Sally

Answer 8

Convert them both to the data type they should be, and make the comparison.

This will reveal not only the issue of the data being the same, but also if there is an issue in the conversions.

Answer 9

Thank you Kalman Toth and Uri Dimant and Patrick Hurst for providing the information, advice, and feedback that helps to solve this issue.  Your invaluable information helps me to look and try different methods.

Here are my steps to complete and solve the conversion process from datetime to varchar:

--step 1
select DOB, CAST(DOB AS varchar(12)) AS Expr1, DATEPART(YYYY, DOB) AS Year, DATEPART(MM, DOB) AS Month, DATEPART(dd, DOB) AS Day
from table1
--step 2
SELECT DOB, Expr1, Year, Month, Day, CONVERT(varchar(4), Year) AS YYYY, Month AS Expr3, RIGHT('0' + CONVERT(varchar, Month), 2)
                  AS MM, Day AS Expr4, RIGHT('0' + CONVERT(varchar, Day), 2) AS DD
from table2
--step 3
SELECT DOB, Expr1, YYYY + MM + DD AS DOB2
from table3

step 1
convert dob from datetime to varchar which will shows Mon Day, and Year format
the datepart will display the year in YYYY, month in MM, and day in DD

step 2
convert year to varchar
convert month to varchar and add leading zero
convert day to varchar and add leading zero

step 3
concatenate year, mm, and dd to convert to varchar

The DOB2 field is now a varchar(8) data and can be used to compare with the birth_date data.