How do I query dates stored in a varchar field?
-
Hi everyone, I am attempting to read records from a table based on a date range, however, the dates are stored in a varchar field. The format of the date is 2005-09-01 10:50:00. How can I get around the fact that the vendor used the wrong data type? Your help is greatly appreciated.
-
Hi everyone, I am attempting to read records from a table based on a date range, however, the dates are stored in a varchar field. The format of the date is 2005-09-01 10:50:00. How can I get around the fact that the vendor used the wrong data type? Your help is greatly appreciated.
Depends on what daabase you are using. If it's MSSQL you could cast/convert the varchar to a date before comparing it in the query:
select * from [PoorlyDesignedTable] where Cast([ShouldBeADateTimeField] as Datetime) = MyDateAbsolute faith corrupts as absolutely as absolute power Eric Hoffer All that is necessary for the triumph of evil is that good men do nothing. Edmund Burke -
Depends on what daabase you are using. If it's MSSQL you could cast/convert the varchar to a date before comparing it in the query:
select * from [PoorlyDesignedTable] where Cast([ShouldBeADateTimeField] as Datetime) = MyDateAbsolute faith corrupts as absolutely as absolute power Eric Hoffer All that is necessary for the triumph of evil is that good men do nothing. Edmund BurkeRob thanks for your quick response! Yes it is MSSQL 2000. Now please pardon my ignorance, but, if I have records with dates greater than 9/1/05, the following statement should return all those records rigt?
SELECT field7, field13, field14 FROM myTable WHERE (CAST(field14 AS Datetime) >= '2005-09-01') AND (field7 = 'Work Order')But my result set is empty. What am I missing? -
Rob thanks for your quick response! Yes it is MSSQL 2000. Now please pardon my ignorance, but, if I have records with dates greater than 9/1/05, the following statement should return all those records rigt?
SELECT field7, field13, field14 FROM myTable WHERE (CAST(field14 AS Datetime) >= '2005-09-01') AND (field7 = 'Work Order')But my result set is empty. What am I missing?SQL Server will return those records for which CAST(field14 AS Datetime) is greather than '2005-09-01' only if value for filed7 is equal with 'Work Order' Your result may be empty if settings for database COLLATION or field7 COLLATION is case sensitive CS. Try to use this condition: ... AND UPPER(field7) = 'WORK ORDER' Be attention at spaces between WORK and ORDER ! Also, the condition for date may be rewrite thus: MONTH( field4 ) >= 9 AND YEAR( field4 ) >= 2005
-
Depends on what daabase you are using. If it's MSSQL you could cast/convert the varchar to a date before comparing it in the query:
select * from [PoorlyDesignedTable] where Cast([ShouldBeADateTimeField] as Datetime) = MyDateAbsolute faith corrupts as absolutely as absolute power Eric Hoffer All that is necessary for the triumph of evil is that good men do nothing. Edmund Burke -
If all the values from field4 are in Y-M-D HH:MM format then SQL Server will implicit conversion VARCHAR field to DATETIME.
-
Rob thanks for your quick response! Yes it is MSSQL 2000. Now please pardon my ignorance, but, if I have records with dates greater than 9/1/05, the following statement should return all those records rigt?
SELECT field7, field13, field14 FROM myTable WHERE (CAST(field14 AS Datetime) >= '2005-09-01') AND (field7 = 'Work Order')But my result set is empty. What am I missing? -
SQL Server will return those records for which CAST(field14 AS Datetime) is greather than '2005-09-01' only if value for filed7 is equal with 'Work Order' Your result may be empty if settings for database COLLATION or field7 COLLATION is case sensitive CS. Try to use this condition: ... AND UPPER(field7) = 'WORK ORDER' Be attention at spaces between WORK and ORDER ! Also, the condition for date may be rewrite thus: MONTH( field4 ) >= 9 AND YEAR( field4 ) >= 2005
Thanks everyone for your help! I ended up using the following statement:
WHERE (UPPER(field7) = 'WORK ORDER') AND (field14 BETWEEN @startdate AND @enddate)Following airbus' suggestion to useUPPER, and documentation I found in SQL Sever Bible Thanks again!