Convert varchar to date on a column

I have a table with a column defined as a nvarchar. The strings contained in the columns are in the form of YYYYMMDD. I searched the forums here and believe that if I have a date as a string then YYYYMMDD is the correct format for a date stored as string. However, I think I need to store the date as a Date type for selecting, sorting, searching, and indexing. What is the best method of converting the entire column to a Date type from a nvarchar type with the assumption that all the string dates are in the same YYYYMMDD format? When I tried to modify the table using the Managment Studio Express, I get a warning: "Data might be lost converting column 'Date' from 'nvarchar(50)'.

Thanks!

DeBug

Update: I just noticed that all the text fields have double quotes at the start and end of each text string so my so called YYYYMMDD is really "YYYYMMDD". So I guess the answer would need to include how to scrub the " with an UPDATE command. I used the DTS that came with Express to import the data from a flat file.

Moving to TSQL forum. There are many ways i can think of doing this, one of the easiest would be to create a new column, call it DateCol2 with datetime datatype, and then do an update statement on the table to set this column properly. it might involve some parsing, but if you make a mistake, you can always try it again since this is just a new column. Once you've updated it correctly, you can drop the original column and rename the new column appropriately.

Someone here can probably give you the proper update statement.

|||

With the help of Google, a little trial and error, and two cups of coffee here is what worked:

I used the DTS Wizard (Hey! SQL Express has a DTS wizard!) to select the flat file with the source data but entered the "Text Qualifier" as a single double quote character. I had previously left that field blank on the first 14 attempts :) Once the column was populated with the string as YYYYMMDD and not "YYYYMMDD" with the quotes, the modify table colulmn converted the strings to dates. I still got the warning message but a quick glance at the table showed a successful conversion. I will format the dates to mm/dd/yyyy for reports and the end user application on the fly (I think...).

I would still like to know what are the advantages, if any, of using that column as a Date type versus a Varchar type. Anyone?

Doug DeBug

|||

For starters, with datetime datatype you can do arithmatic operations using built-in functions (dateadd, datediff, datefirst, etc.), you can also reference it in multiple ways - "December 27, 2006" or "12/27/2006", or using convert(). You can read more about this datatype in Books Online topic "Using Date and Time Data", http://msdn2.microsoft.com/en-us/library/ms180878.aspx.

|||

Create Table #test(mydate varchar(25))

Alter Table #test

Alter column mydate datetime

drop table #test

Adamus

|||

Doug DeBug wrote:

With the help of Google, a little trial and error, and two cups of coffee here is what worked:

I used the DTS Wizard (Hey! SQL Express has a DTS wizard!) to select the flat file with the source data but entered the "Text Qualifier" as a single double quote character. I had previously left that field blank on the first 14 attempts :) Once the column was populated with the string as YYYYMMDD and not "YYYYMMDD" with the quotes, the modify table colulmn converted the strings to dates. I still got the warning message but a quick glance at the table showed a successful conversion. I will format the dates to mm/dd/yyyy for reports and the end user application on the fly (I think...).

I would still like to know what are the advantages, if any, of using that column as a Date type versus a Varchar type. Anyone?

Doug DeBug

Using the field as a datetime vs. varchar() is the logical thing to do. There's no advantage but only a disadvantage. You can't use operators as comparison and you remove the possibility of using BETWEEN.

Adamus

|||

You can do the convert using UPDATE statement and then do ALTER like:

update tbl

set your_col = replace(your_col, '"', '')

go

alter tbl alter column your_col smalldatetime

go

As for the benefits of using datetime/smalldatetime vs varchar type, the obvious ones are:

1. Appropriate type checking and domain enforcement

2. Better performance

3. Compatibility with other built-in date functions (although they accept strings it depends on the format)

4. Storage depending on how you store the date value in character format

5. Ordering semantics that follow the date or datetime rules

There are cases where you may want to store datetime values in more compact form than using datetime/smalldatetime but those need to be done with care & careful consideration.

|||

Thanks for the example. I will have not used T-SQL as much I should but have relied on the GUIs in the past. I will need a solution to update the customer's database at least once per week. Getting the things like quoted strings out of the way now is a plus.

Regards,

DeBug