hwo to match/convert the date and character variables

[Kwon]

Hello,
I run the DTS to copy data from Progress to SQL Server. How can match/convert the date variables to check.
The field p-date as format 'mm-dd-year' . It has the value of 10/09/2001.
The field s-date as varchar format. It has the value 2001-10-09.
How can use the where condition ( Select ...... WHERE p-date = s-date.)

Thanks

 

[Guest_imported]

Hi

Just try CONVERT function like
Select ...... WHERE p-date = convert(datetime, s-date, 101)

101 is USA date format (mm/dd/yyyy)

 

[tlbroadbent]

1) Don't store dates in SQL Server as varchar or any other string/character format. Store dates as datetime or smalldatetime data types. Searches will be faster. Comparison of data will be easier. Ordering of data will be correct.

2) Igor Olehn correctly suggested using convert in order to compare the columns. However, you need to use the style number that matches the date format in order for the conversion to work properly.

Example: convert char data type columns to datetime data type for comparison.

Where convert(datetime,p-date,10) =
convert(datetime,s-date,120)

10 is the style number for the mm-dd-yy format
120 is the style number for the yyyy-mm-dd format

See "Cast and Convert" in SQL BOL for more information on converting dates and using date styles.

Cast and Convert: Including table of datetime styles

http://msdn.microsoft.com/library/en-us/tsqlref/ts_ca-co_2f3o.asp

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.