Confusing Results

I am working with some data in the form of dates formatted as mm/dd/yy. Ove
r
the years, people have made mistekes in entering dates using formats like
mmdd. I am using a CASE statement to sort through these and apply the
correct formatting to them before I parse them to smalldatetime. I have the
following code:
SELECT
CASE
WHEN LEN([DateOut]) = 8 THEN
CAST((LEFT([DateOut], 6) + '20' + RIGHT([DateOut], 2)) AS smalldatetime)
WHEN [DateOut] <> '' THEN
'-- ' + [DateOut] + ' --'
ELSE
'** Empty **'
END AS [DateOut2]
FROM [TOFC].[dbo].[temp_hold] hold
By checking the length of the string, I can make sure that the date is in
the right format, and is faster than using LIKE '__/__/__'. There have been
a
few cases when the date was eight characters long but in the incorrect
format, but those have been fixed. Here is some sample data:
[DateOut]: '02/28/05', '5/21/05', '8/23/05', '0521', '050 8AM'
When I run the query, I get 'Syntax error converting character string to
smalldatetime data type.' However, when I put LEN([DateOut]) = 8 in the
WHERE clause, it queries with no errors. I have simmilar code blocks in
other parts of the SELECT that do the same thig, but to different fields, an
d
have no problems. I just can't figure out why the CASE block isn't catching
the wrong length strings. Does anyone have any idea why this particular
block is not working? Where is one of the CASE blocks that is working, just
for reference:
CASE
WHEN LEN([SortDate]) = 4 THEN
CAST((LEFT([SortDate], 2) + '/' + RIGHT([SortDate], 2) + '/2005') AS
smalldatetime)
WHEN [SortDate] <> '' THEN
'-- ' + [SortDate] + ' --'
END AS [SortDate]
Thanks in advance.
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/AppsChris Lieb wrote:
> I am working with some data in the form of dates formatted as
> mm/dd/yy. Over the years, people have made mistekes in entering
> dates using formats like mmdd. I am using a CASE statement to sort
> through these and apply the correct formatting to them before I parse
> them to smalldatetime. I have the following code:
> SELECT
> CASE
> WHEN LEN([DateOut]) = 8 THEN
> CAST((LEFT([DateOut], 6) + '20' + RIGHT([DateOut], 2)) AS
> smalldatetime) WHEN [DateOut] <> '' THEN
> '-- ' + [DateOut] + ' --'
> ELSE
> '** Empty **'
> END AS [DateOut2]
> FROM [TOFC].[dbo].[temp_hold] hold
> By checking the length of the string, I can make sure that the date
> is in the right format, and is faster than using LIKE '__/__/__'.
> There have been a few cases when the date was eight characters long
> but in the incorrect format, but those have been fixed. Here is some
> sample data:
> [DateOut]: '02/28/05', '5/21/05', '8/23/05', '0521', '050 8AM'
> When I run the query, I get 'Syntax error converting character string
> to smalldatetime data type.' However, when I put LEN([DateOut]) = 8
> in the WHERE clause, it queries with no errors. I have simmilar code
> blocks in other parts of the SELECT that do the same thig, but to
> different fields, and have no problems. I just can't figure out why
> the CASE block isn't catching the wrong length strings. Does anyone
> have any idea why this particular block is not working? Where is one
> of the CASE blocks that is working, just for reference:
> CASE
> WHEN LEN([SortDate]) = 4 THEN
> CAST((LEFT([SortDate], 2) + '/' + RIGHT([SortDate], 2) + '/2005') AS
> smalldatetime)
> WHEN [SortDate] <> '' THEN
> '-- ' + [SortDate] + ' --'
> END AS [SortDate]
> Thanks in advance.
For a date-only conversion you should use YYYYMMDD as the format as it's
the only format that is portable.
It sounds like you might have a transposed month and day in one of the
dates. Why not output to the screen each date as its about to be
converted. That will show you the date with the problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||To begin, there is a function called IsDate() that returns 1 if the
expression can be converted into a type of datetime. If the users have been
able to enter whatever they wish free form, then you need to LTrim() and
RTrim() spaces when checking the length. For example, len("0522 ") = 8,
but LTrim(RTrim("0522 ")) = 4. Also, you may need to take NULL values
into consideration.
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:7A367A9D-9003-4ABF-B8F7-0C60F8150762@.microsoft.com...
> I am working with some data in the form of dates formatted as mm/dd/yy.
Over
> the years, people have made mistekes in entering dates using formats like
> mmdd. I am using a CASE statement to sort through these and apply the
> correct formatting to them before I parse them to smalldatetime. I have
the
> following code:
> SELECT
> CASE
> WHEN LEN([DateOut]) = 8 THEN
> CAST((LEFT([DateOut], 6) + '20' + RIGHT([DateOut], 2)) AS smalldatetime)
> WHEN [DateOut] <> '' THEN
> '-- ' + [DateOut] + ' --'
> ELSE
> '** Empty **'
> END AS [DateOut2]
> FROM [TOFC].[dbo].[temp_hold] hold
> By checking the length of the string, I can make sure that the date is in
> the right format, and is faster than using LIKE '__/__/__'. There have
been a
> few cases when the date was eight characters long but in the incorrect
> format, but those have been fixed. Here is some sample data:
> [DateOut]: '02/28/05', '5/21/05', '8/23/05', '0521', '050 8AM'
> When I run the query, I get 'Syntax error converting character string to
> smalldatetime data type.' However, when I put LEN([DateOut]) = 8 in the
> WHERE clause, it queries with no errors. I have simmilar code blocks in
> other parts of the SELECT that do the same thig, but to different fields,
and
> have no problems. I just can't figure out why the CASE block isn't
catching
> the wrong length strings. Does anyone have any idea why this particular
> block is not working? Where is one of the CASE blocks that is working,
just
> for reference:
> CASE
> WHEN LEN([SortDate]) = 4 THEN
> CAST((LEFT([SortDate], 2) + '/' + RIGHT([SortDate], 2) + '/2005') AS
> smalldatetime)
> WHEN [SortDate] <> '' THEN
> '-- ' + [SortDate] + ' --'
> END AS [SortDate]
> Thanks in advance.
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||Chris,
The result type of a case expression will be the highest precedence. Because
you are casting one expression to smalldatetime, then sql server will try to
cast:

> WHEN [DateOut] <> '' THEN
> '-- ' + [DateOut] + ' --'
> ELSE
> '** Empty **'
"'-- ' + [DateOut] + ' --'" and "'** Empty **'" to smalldatime, and this
will give an error. If you use varchar for all result_expressions, then you
will not get any error.
AMB
"Chris Lieb" wrote:

> I am working with some data in the form of dates formatted as mm/dd/yy. O
ver
> the years, people have made mistekes in entering dates using formats like
> mmdd. I am using a CASE statement to sort through these and apply the
> correct formatting to them before I parse them to smalldatetime. I have t
he
> following code:
> SELECT
> CASE
> WHEN LEN([DateOut]) = 8 THEN
> CAST((LEFT([DateOut], 6) + '20' + RIGHT([DateOut], 2)) AS smalldatetime)
> WHEN [DateOut] <> '' THEN
> '-- ' + [DateOut] + ' --'
> ELSE
> '** Empty **'
> END AS [DateOut2]
> FROM [TOFC].[dbo].[temp_hold] hold
> By checking the length of the string, I can make sure that the date is in
> the right format, and is faster than using LIKE '__/__/__'. There have bee
n a
> few cases when the date was eight characters long but in the incorrect
> format, but those have been fixed. Here is some sample data:
> [DateOut]: '02/28/05', '5/21/05', '8/23/05', '0521', '050 8AM'
> When I run the query, I get 'Syntax error converting character string to
> smalldatetime data type.' However, when I put LEN([DateOut]) = 8 in the
> WHERE clause, it queries with no errors. I have simmilar code blocks in
> other parts of the SELECT that do the same thig, but to different fields,
and
> have no problems. I just can't figure out why the CASE block isn't catchi
ng
> the wrong length strings. Does anyone have any idea why this particular
> block is not working? Where is one of the CASE blocks that is working, ju
st
> for reference:
> CASE
> WHEN LEN([SortDate]) = 4 THEN
> CAST((LEFT([SortDate], 2) + '/' + RIGHT([SortDate], 2) + '/2005') AS
> smalldatetime)
> WHEN [SortDate] <> '' THEN
> '-- ' + [SortDate] + ' --'
> END AS [SortDate]
> Thanks in advance.
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||Commenting out the second WHEN clause and the ELSE clause got rid of the
error. If the CASE black uses type precidence to decide which data type to
be, why does a virtually identical CASE block that operates on another field
operate with no problems?
Chris
"Alejandro Mesa" wrote:
> Chris,
> The result type of a case expression will be the highest precedence. Becau
se
> you are casting one expression to smalldatetime, then sql server will try
to
> cast:
>
> "'-- ' + [DateOut] + ' --'" and "'** Empty **'" to smalldatime, and this
> will give an error. If you use varchar for all result_expressions, then yo
u
> will not get any error.
>
> AMB
>
> "Chris Lieb" wrote:
>|||I decided to place the functionality in a function since I call it for seven
fields. Here is the code:
CREATE FUNCTION [dbo].[STRtoDATE] (@.str nvarchar(20))
RETURNS sql_variant AS
BEGIN
DECLARE @.tmp sql_variant
IF @.str LIKE '__/__/__'
SET @.tmp = LEFT(@.str, 6) + '20' + RIGHT(@.str, 2)
ELSE IF @.str LIKE '____'
SET @.tmp = LEFT(@.str, 2) + '/' + RIGHT(@.str, 2) + '/' + '2005'
ELSE IF @.str <> ''
SET @.tmp = '-- ' + @.str + ' --'
ELSE
SET @.tmp = '** EMPTY **'
IF ISDATE(CAST (@.tmp AS nvarchar)) = 1
SET @.tmp = CAST(@.tmp AS smalldatetime)
RETURN @.tmp
END
It porbably could be done better since I resorted to using sql_variant as
the return type. I want to add a time parameter, but make it optional since
only one field needs this functionality. If it is unused, it can default to
00:00:00. How do I go about declaring an optional parameter to a function i
n
T-SQL?
Chris|||The same way as you do with a sp. You have to pass a value or use keyword
'default', you can not omit the parameter.
Example:
use northwind
go
create function dbo.ufn_f1 (
@.d datetime = '17530101'
)
returns datetime
as
begin
return (@.d)
end
go
select dbo.ufn_f1(default)
go
drop function dbo.ufn_f1
go
AMB
"Chris Lieb" wrote:

> I decided to place the functionality in a function since I call it for sev
en
> fields. Here is the code:
> CREATE FUNCTION [dbo].[STRtoDATE] (@.str nvarchar(20))
> RETURNS sql_variant AS
> BEGIN
> DECLARE @.tmp sql_variant
> IF @.str LIKE '__/__/__'
> SET @.tmp = LEFT(@.str, 6) + '20' + RIGHT(@.str, 2)
> ELSE IF @.str LIKE '____'
> SET @.tmp = LEFT(@.str, 2) + '/' + RIGHT(@.str, 2) + '/' + '2005'
> ELSE IF @.str <> ''
> SET @.tmp = '-- ' + @.str + ' --'
> ELSE
> SET @.tmp = '** EMPTY **'
> IF ISDATE(CAST (@.tmp AS nvarchar)) = 1
> SET @.tmp = CAST(@.tmp AS smalldatetime)
> RETURN @.tmp
> END
> It porbably could be done better since I resorted to using sql_variant as
> the return type. I want to add a time parameter, but make it optional sin
ce
> only one field needs this functionality. If it is unused, it can default
to
> 00:00:00. How do I go about declaring an optional parameter to a function
in
> T-SQL?
> Chris|||Thanks. Here is my final code:
CREATE FUNCTION [dbo].[STRtoDATE] (@.date nvarchar(20), @.time nvarchar(20) =
'x', @.year integer = 0)
RETURNS sql_variant AS
BEGIN
DECLARE @.tmpdate nvarchar(20)
DECLARE @.tmptime nvarchar(20)
DECLARE @.tmp sql_variant
IF @.date LIKE '__/__/__' BEGIN
IF RIGHT(@.date, 2) BETWEEN 80 AND 99
SET @.tmpdate = LEFT(@.date, 6) + '19' + RIGHT(@.date, 2)
ELSE
SET @.tmpdate = LEFT(@.date, 6) + '20' + RIGHT(@.date, 2)
END
ELSE IF @.date LIKE '____' BEGIN
IF @.year <> 0
SET @.tmpdate = LEFT(@.date, 2) + '/' + RIGHT(@.date, 2) + '/' + CAST(@.year
AS nvarchar)
ELSE
SET @.tmpdate = LEFT(@.date, 2) + '/' + RIGHT(@.date, 2) + '/' + '2005'
END
ELSE IF @.date LIKE '______' BEGIN
IF RIGHT(@.date, 2) BETWEEN 80 AND 99
SET @.tmpdate = LEFT(@.date, 2) + '/' + SUBSTRING(@.date, 3, 2) + '/19' +
RIGHT (@.date, 2)
ELSE
SET @.tmpdate = LEFT(@.date, 2) + '/' + SUBSTRING(@.date, 3, 2) + '/20' +
RIGHT (@.date, 2)
END
ELSE IF @.date <> ''
SET @.tmpdate = '-- ' + @.date + ' --'
ELSE
SET @.tmpdate = '** EMPTY **'
IF @.time LIKE '__:__'
SET @.tmptime = @.time
ELSE IF @.time <> ''
SET @.tmptime = '-- ' + @.time + ' --'
ELSE
SET @.tmptime = '** EMPTY **'
IF @.time = 'x'
SET @.tmp = @.tmpdate
ELSE
SET @.tmp = @.tmpdate + ' ' + @.tmptime
IF CAST(@.tmp AS nvarchar) LIKE '__/__/____ __:__' OR CAST(@.tmp AS nvarchar)
LIKE '__/__/____'
SET @.tmp = CAST(@.tmp AS smalldatetime)
ELSE IF @.tmp = '** EMPTY **' OR @.tmp = '** EMPTY ** ** EMPTY **'
SET @.tmp = ''
RETURN @.tmp
END
Again, not the prettiest, but it gets the job done, and in an acceptable tim
e.
Thanks everyone.
Chris