Converting char(8) to a date value (dd/mm/yy)

[Annelies]

Hi

The scenario is:

I am storing date information in a database in the format mm/dd/yy, in a char(8) field. (So, a piece of example data might be '01/03/03')

But when I run a report over multiple months (ie, everything from %/01/03 or %/02/03) it does not sort the dates correctly, because (understandly) the database thinks it is sorting strings, not dates. So I get 01/01/03, 01/02/03, 02/01/03, 02/02/03, etc...

So, I think I need to convert the char field into a date, for the purposes of this report.

However, I'm finding that:


SELECT CONVERT(DATETIME, '01/03/03') returns 2003-01-03 00:00:00.000, which the system reads as the 3rd Jan 2003, instead of 1st March 2003.

and if I try to use a 'style' (ie, 103 which is British/French) then I get the following:


SELECT CONVERT(DATETIME, '01/03/03', 103) = Syntax error converting datetime from character string.

Can anyone help? Am I even going about this the right way??

Thanks in advance!

Annelies

 

[ks2000]

hi,

try like this
use cast(checkdate as datetime) for converting char to datetime

and further if you want to converty to style 103 use the following

SELECT convert(varchar,cast(checkdate as datetime),103)
FROM [dbo].[tempta]

 

[Annelies]

I'm really confused.

When I use this using...

DECLARE @datechar as varchar(8)
SET @datechar = '01/03/03'
SELECT (CONVERT(char(10),CAST(@datechar as datetime),103))

it works beautifully.

But when I try to use it in a select statement from my database...

SELECT (CONVERT(char(10),CAST(SaleDate as datetime),103)) FROM Sales

it gives the following error message...

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

What's going on?? Does anyone know?

Thanks!

 

[stravis]

This error occurs when you have an invalid date value that you are attempting to convert. To find all of your invalid dates in your SaleDate column use the following query.

SELECT * FROM Sales WHERE ISDATE(Sales.SaleDate) = 0

This should find all of your invalid entries so that you can correct these dates. After making the corrections your query will run fine.

 

[ks2000]

Yes , your table contains some invalid date records, so first of all find out those records and run the same query

 

[Annelies]

I'm having it come back with any date that does not make sense given an american date format.

For example, in British format, 28/03/03 makes perfect sense. But in american format, it's not possible. So it's coming back with an error.

I think this is going to prove tricky!

I've put off thus far using datepart to pick apart the date, and reassmble it in american date format, so that my conversion functions work correctly. But I can't see any alternatives... can anyone else???

Thanks

Annelies

 

[SQLBill]

Try this:

SET DATEFORMAT dmy
--then try the conversion to datetime
SELECT (CONVERT(char(10),CAST(SaleDate as datetime),103)) FROM Sales


The SET DATEFORMAT lets SQL Server know how to interpret the date information. Refer to the Books OnLine for more information.

-SQLBill

 

[JamesLean]

I'm slightly confused as your original question says you are using mm/dd/yy format but I get the impression that the dates are actually in UK format - dd/mm/yy?

Anyhow, the syntax error is because you are using style 103 - which is expecting a 4-digit year rather than style 3 which expects 2 digits.

This query should work, providing all your dates are in correct dd/mm/yy format:

SELECT CONVERT(datetime, salesdate, 3)
FROM sales

If your dates are in US mm/dd/yy format then use style 1 rather than 3.

Also, this shows why it's usually a good idea to store dates in a datetime field rather than a varchar field.

--James