SQL Server: Truncating date (type varchar) to remove time

[fionama]

Hi,
I'm a beginner, so thanks in advance for helping me!
I'm using SQL server database and Busobj XI R2.

I have a number of date fields of type varchar, that output values in the YYYY-MM-DD HH:MM format.

I want to create a report where the user prompts for a start date and an end date, and would like that just the date (preferably in the DD-MM-YYYY format) appears.

I am testing this out on a small table I have created myself, called AEReturns, with field names (i)AEMeasureDate (type varchar), and (ii)ZeroToSix (type int).

I have tried suggestions previous posted, such as:
cast(PRT_DW.warehouse.AEReturns.AEMeasureDate as DateTime)

which gives me a value format 9/6/2006 8:00:00 AM (dd/mm/...) when prompted in the report,

and,

convert(datetime,substring(convert(varchar,PRT_DW.warehouse.AEReturns.AEMeasureDate),1,4)
+'-'+ substring(convert(varchar,PRT_DW.warehouse.AEReturns.AEMeasureDate),6,2)
+'-'+ substring(convert(varchar,PRT_DW.warehouse.AEReturns.AEMeasureDate),9,2))

which gives me a value format 9/6/2006, and appends a 12:00:00 AM to each date in the list of values.

Would be very appreciative if anyone could help...

 

[blom0344]

SQL server does not have a specific datatype that only stores dates. If you do not specify the time component it will default that value to midnight (= 12:00:00 AM).

Perhaps a different approach is to NOT specify a list of values , but indicate the format in the prompt definition, like:

'Enter startdate (format dd/mm/yy 00:00:00)'

'Enter enddate (format dd/mm/yy 24:00:00)'

Ties Blom