Cast problem
-
Hi folks
I am trying to use:
cast(cast({A} as varchar) as datetime)
The filed {A} is an int field typical of type: 20030602
I have now created a new datetime field and the properties contains the cast statement above..
But when trying to execute I get the message
"error converting datetime from character string."
When I do this in winsql I get positive result like:
QUERY:
select distinct
[service date],
cast(cast([service date] as varchar) as datetime)
from
[errormode detailed]
where
[service date] = '20030602'
RESULT:
service date
------------ -----------------------
20030602 2003-06-02 00:00:00.000
1 Row(s) affected
Anyone care to give me a tip on this.
Best regards
Dan
-
Need to correct my self abit.
I said it worked well under winsql. That is a truth with modifications...
Only when I add the where clause does it work, if I exclude the the where clause, it fails.
And of course, I need it to work without the where caluse.
Hope to hear form somebody regarding this issue.
Thank you for your time
Best Regards
Dan
-
Check that all your input data is formatted as yyyymmdd.
select cast(cast(20031224 as varchar) as datetime)
will work
select cast(cast(20032412 as varchar) as datetime)
will give conversion error
Far away is close at hand in the images of elsewhere.
Anon. -
Hi,
i think you have problem with your data. Try query
select [service date] from [errormode detailed]
and check int value from [service date]. I hope you'll find int value which isn't in right format.
Kindest Regards,vbenus
-
Thanks for reply.
used:
select distinct [service date] from [errormode detailed]
It resulted in two kinds of posts:
Service date
0
20030101
20030102
.....
Then I made this to controll any 0 posts
CASE WHEN ( {A} is null)
THEN 0
ELSE cast(cast({A} as varchar) as datetime)
END)
But no good....
Regards
Dan
-
You cannot convert character zero to a datetime.
You can convert integer zero to datetime as in
select cast(0 as datetime)
which will give you 1900-01-01 00:00:00.000
You will have to change zeros to null or '1900-01-01'. Whichever you use will depend on what is using the output and what for.
Far away is close at hand in the images of elsewhere.
Anon. -
There is 0 in your result set. And that is problem. You cannot use 0 in your query cast(cast({A} as varchar) as datetime)
try: select cast(cast(0 as varchar) as datetime)
and you'll get error.
Kindest Regards,vbenus
-
case
when ([service date] = 0) then null else cast(cast([service date] as varchar) as datetime) end
This just results in an out of range
Dan
-
again this points to a data issue
run this
select [service date]
from
where [service date] > 0
and isdate([service date]) = 0
to what values sql considers not a date
Far away is close at hand in the images of elsewhere.
Anon. -
Dan,
This:
case when ([service date] = 0) then null else cast(cast([service date] as varchar) as datetime) end
Was so close. Try
case when ([service date] = 0) then '1/1/1900' else cast(cast([service date] as varchar) as datetime) end
The conversion error was the null you were injecting, why do that when you can set it to 1/1/1900 as place holder for zero.
You might run in to many other errors such as a data set of 20030229 (no leap year in 2003); short data sets (2004011); day field of 31 in a month with 30 days etc.
My prefered method is to split out the int/varchar 20040622 into year month and day columns, check them, then reassemble them as a datetime in the correct format : 06/22/2004. This sounds like a lot more work, but the first time you run through 2-3 million rows and try to find the one bad dataset that will not convert to a date time it becomes apparent why this is better.
here is a snippet of code I use for fixing a date of birth field, at this point I've got a dateofbirth column as 20040622, and dayofbirth, monthofbirth, yearofbirth columns have been previously made and populated with the correct parts:
[DateOfBirth] = CASE WHEN a.[dayofbirth] = '' or a.[monthofbirth] = '' or a.[yearofbirth] = ''
or a.[dayofbirth] is null or a.[monthofbirth] is null or a.[yearofbirth] is null
or a.[DateOfBirth]='000000' or a.[monthofbirth]='00' or a.[dayofbirth] = '00'
or a.[yearofbirth]='0000'THEN '01'
ELSE a.[MonthOfBirth] END
+ '/' +
CASE WHEN a.[dayofbirth] = '' or a.[monthofbirth] = '' or a.[yearofbirth] = ''
or a.[dayofbirth] is null or a.[monthofbirth] is null or a.[yearofbirth] is null
or a.[DateOfBirth]='000000' or a.[monthofbirth]='00' or a.[dayofbirth] = '00'
or a.[yearofbirth]='0000'
THEN '01'
WHEN CONVERT(INT,a.[dayofbirth]) > 28 and a.[monthofbirth] = '02' and a.[yearofbirth]
not in ('1900','1904', '1908', '1912', '1916', '1920', '1924', '1928', '1932'
, '1936', '1940', '1944', '1948', '1952', '1956', '1960', '1964', '1968'
, '1972', '1976', '1980', '1984', '1988', '1992', '1996', '2000', '2004')
THEN '28'
WHEN CONVERT(INT,a.[dayofbirth]) > 30 and a.[monthofbirth] in ('04', '06', '09', '11')
THEN '30'
ELSE a.[DayOfBirth] END
+ '/' +
CASE WHEN a.[dayofbirth] = '' or a.[monthofbirth] = '' or a.[yearofbirth] = ''
or a.[dayofbirth] is null or a.[monthofbirth] is null or a.[yearofbirth] is null
or a.[DateOfBirth]='000000' or a.[monthofbirth]='00' or a.[dayofbirth] = '00'
or a.[yearofbirth]='0000' THEN '1900'
ELSE a.[YearOfBirth] END,
It's not pretty, but it does work. I get a lot of hand entered data that is full of bad dates, using this I have not had it error out on me once...
Thanks, and don't forget to Chuckle
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply