How to: Extract only Date Part From DateTime

It is common practice to extract only date part from datetime format, such as for a datetime of 2014-05-30 15:31:58.860, only 2014-05-30 is returned. Today we will show three main methods to choose from.

Method 1:

SELECT convert(DATE, GETDATE() )

Method 2:

SELECT CONVERT(VARCHAR(10),GETDATE(),126)

Method 3:

SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))

will mask the time part as 00:00:00.000.  It sometimes is regarded as better performance here.

How to: Load DATE Type Data by SSIS

We have discussed Implicit conversion from varchar to datetime by SQL Server, today I will show you how to take advantage of this kind of implicit conversion when loading date type data using SSIS. 

Problem

Every business needs time dimension to measure its profit or growth. Date-related columns can be birth date of persons, order dates or ship dates of products, prescription date, etc. However, date-related columns can be tricky to load because there seems always some data issues with them. For example, suppose we have a source file with PRESCRIPTION_DATE column in format of DD-MMM-YYYY  in source files. Some values are just empty or set as a dot (that might come from SAS) while the destination table defines this column as date type or datetime type.

How to load date-type columns?

Step 1. Define all date-related columns as VARCHAR in flat file connection manager and load all data  as VARCHAR first in Flat File Source.

Step 2.  Use derived column to clean up those dot values or other issues and to "transform" the data.

	Solution 1:	Define a new derived column [Conv_DateColumn] with expression set as below:  LEN(TRIM(PRESCRIPTION_DATE)) < 2 ? NULL(DT_DBDATE) : (DT_DBDATE)(SUBSTRING(PRESCRIPTION_DATE,1,2) + "-" + SUBSTRING(PRESCRIPTION_DATE,3,3) + "-" + SUBSTRING(PRESCRIPTION_DATE,6,4))
	Solution 2:	Define a new derived column [Conv_DateColumn] as a (DT_STR,10,1252), in format of "YYYY-MM-DD".

             

Step 3. When you do Mappings for OLE DB Destination Editor, map the [Conv_DateColumn] to the dateColumn with date or datetime as column type.

That's it!

Implicit Conversion From Varchar to Datetime

Sometimes a simple little thing may bring us some sights. Today I'd like to share with you something related to implicit conversion in sql server.

Problem

A colleague brought me this scenario: when she tried to update some table by joining with other tables, She got the following error:

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

So the action to work around this is to change data type of the related column from varchar(10) to datetime by just using ALTER COLUMN function. However, another error jumped out:

Msg 241, Level 16, State 1, Line 1

Conversion failed when converting date and/or time from character string.

The statement has been terminated.

What? This is kind of common practices for me as well. What did the error come from?

So I went ahead to dig into the date column that was defined as varchar(10). The moment I saw the data, I got some hint. In that column, the date was displayed as "DD-MM-YYYY". 

Solution

After changing the format from "DD-MM-YYYY" to "YYYY-MM-DD" by the following script:

UPDATE schemaName.tableName

SET datecolumn = SUBSTRING(datecolumn,7,4)+'-'+ SUBSTRING(datecolumn,4,2)+'-' +SUBSTRING(datecolumn,1,2)

there is no more problem to use ALTER COLUMN to update that column's data type from Varchar to Datetime. 

Conclusion

 As a summary, SQL Server only allows implicit conversion from Varchar to Datetime when the date format is "YYYY-MM-DD". If you want to learn more about implicit/explicit data conversion, please read technet's article: Data Type Conversion.