DateTime to Character
-
Table 1 - Has a column within it of type datetime.
Table 2 - Has 2 columns within it. One called date , one called time. Both type Character.
How can I join these two tables and with the datetime field? I am pursing trying to change the datetime field to character then doing the join but cant seem to get it.
thanks
Charles
-
Select * From Table1
Join Table2
On
Table1.DateField=Cast(Table2.CharDateField +' '+Table2.CharTimeField as DateTime)
Rohit -
Dang I don't think that you can take two character type fields i.e. date and time and come up with a datetime data type. You can concatenate the two aforementioned columns and derive a date and time however as I recall the datetime type is stored as 2 4 bit integers. You can play with this maybe it will help. I like your question. Some SQL head may have a better answer. you can try this ::
DECLARE @mydate_sm SMALLDATETIMESET @mydate_sm = '4/05/98'SELECT CAST(@mydate_sm AS VARCHAR) AS SM_DATE_VARCHARGODECLARE @mydate DATETIMESET @mydate = '4/05/98'SELECT CAST(@mydate AS BINARY) AS DATE_BINARYGOLets see what kind of racket we stir up here.Jim
-
Well, there are many ways to skin this cat.
However, all have their caveats.
First, you can either convert the datetime to a char/varchar or the chardate + chartime to datetime
and join against that. It's all a matter of syntax.
Next question is if the chardates are in a consistent format..? If not, there's no go.
Then you'll be missing some matches because you can only convert to a single style.
Next is performance penalties.
Since you're using functions on the columns, this will force a table scan, regardless if there may exist any indices
on the columns involved.
In the long run, you may consider to add a datetime column to table two, to better support this join.
In the end, (as always) it depends...
=;o)
/Kenneth
-
Thanks to everyone for the input and help. I will let you know what I find.
-
How about...
select t1.datefield, t2.datefield, t2.timefield, .....
from Table1 t1
inner join Table2 t2
on t2.datefield = convert(char(10), t1.datefield, 101) and
t2.timefield = convert(char(8), t1.datefield, 114)
Assumes your dates are mm/dd/yyyy and time are 24hour format in Table2. Look at CONVERT function for other options.
-
This users on this site have great brain power. I understand that there is also one further consideration. there is a restrain on datetime - before 1753? and after 9999? Would that mean if the person possing the problem had dates and times (in his second table containing two character fields) that didn't follow the datetime data type restriction the joins and converts script would throw an error?
Jim
-
Error is in the eye of the beholder, I suppose. But by definiton there could never be a matching record in Table1. The join 'works', but there are orphaned rows.
Unless there is some sort of application referentatal integrity ensuring there are rows in both tables, this will always be the case.
The designer of the view must decide how to handle these situations. If he is looking for all records in Table1 and any matching records in Table2, change from INNER join to LEFT join.
-
You need to join on the date and time, so you'll have to parse each out of the datetime value separately.
First, parsing date. Look at the "Convert" functions and see if anything applies. If not, then create your own function
2nd, parsing time. There are no built in time parsers, so you'll have to build your own.
Here are some examples that you may or may not have to tweek depending on your formats.
Example Execution:
From DateTime dt (nolock)
JOIN DateAndTime dat (nolock)
on dbo.customdate(dt.DateTime) = dat.Date and
dbo.getTime(dt.Datetime) = dat.Time.
FUNCTIONS:
create function customdate(@DateTime datetime)
returns char(8)
as
begin
return (select cast(datepart(yyyy, @DateTime) as varchar(4)) +
replicate('0', 2 - len(datepart(mm , @DateTime))) + cast(datepart(mm , @DateTime) as varchar(2)) +
replicate('0', 2 - len(datepart(dd , @DateTime))) + cast(datepart(dd , @DateTime) as varchar(2)))
end
create function getTime(@DateTime datetime)
returns char(12)
as
begin
return (select
replicate('0', 2 - len(datepart(hh, @DateTime))) + cast(datepart(hh, @DateTime) as varchar(2)) + ':' +
replicate('0', 2 - len(datepart(mi, @DateTime))) + cast(datepart(mi, @DateTime) as varchar(2)) + ':' +
replicate('0', 2 - len(datepart(ss, @DateTime))) + cast(datepart(ss, @DateTime) as varchar(2)) + '.' +
replicate('0', 3 - len(datepart(ms, @DateTime))) + cast(datepart(ms, @DateTime) as varchar(3)))
end
Signature is NULL
-
Would that mean if the person possing the problem had dates and times (in his second table containing two character fields) that didn't follow the datetime data type restriction the joins and converts script would throw an error?
I would say the answer is yes.
declare @dt varchar(11)
declare @tm varchar(8)
set @dt = '31.12.9999'
set @tm = '23:59:59'
select cast(@dt+ ' ' + @tm as datetime)
set @dt = '01.01.10000'
set @tm = '00:00:01'
select cast(@dt+ ' ' + @tm as datetime)
------------------------------------------------------
9999-12-31 23:59:59.000
(1 row(s) affected)
Server: Nachr.-Nr. 241, Schweregrad 16, Status 1, Zeile 8
Syntaxfehler beim Konvertieren einer Zeichenfolge in eine datetime-Zeichenfolge.
As for the original question I'll second Kenneth. In the long run you would be really better off with a join on two date columns. However, if you need to separately present date and time, use your client app for such presentational stuff.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
That is correct.
Those two years are the limits of the T-SQL datetime datatype.
See BOL for further details on this.
=;o)
/Kenneth
-
Do the producer of the data in table 2 always write the date in the same format, example MM/DD/YYYY?
-
Just be aware that often when applying a function on a column,
the same behaviour as using a cursor happens.
The effect may be that performance becomes abysmal for the query.
If that happens, try it without the function and see if performance changes.
Sometimes functions are a blessing, sometimes not.
=;o)
/Kenneth
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply