CONCATENATING

  • Thread starter Thread starter Welthey
  • Start date Start date
W

Welthey

I am trying to pull 2 fields together in SQL. I have tried the following:

SELECT NewDate, NewTime, NewDAte + ' ' + NewTime as DateTime
From dbo.PurgedRecords

But when it puts the date and time together the Date is different then what
it was nit he original field. Is there something else that I need to be
doing?
 
--
Dave Hargis, Microsoft Access MVP
Should be:
NewDAte & " " & NewTime as DateTime

But that still may not get the results you are actually looking for.
Not a good idea to carry the date and time separately.
 
If I am unable to put the date and the time back together that is fine. The
issue that I am having with that is, I have a table in SQL that is only the
time, it does not carry the 1/1/1900 date. I have another table in SQL that
I need to compare it to and the Time shows with the 1/1/1900 date. Both
fields are set up as date time fields. I'm not sure how to go about making
the 2 fields the same format so that I can match the fields up to run queries.

Any Ideas?
 
Okay, I see the problem now. SQL Server has a time data type that will carry
only time and not include a data value; however, Jet date/time data type
always carrys a time datevalue. So you can use the TimeValue function to do
the compare. It returns only the time portion of a date/time field.

So it would be something like

Timevalue([MdbDateField) = SqlTimeField
 
I'm trying to write this in SQL and I used the following:

SELECT NewDate, NewTime, NewDAte + " " + NewTime as DateTime
From dbo.PurgedRecords;
Click to expand...

New Date NewTime Results
2008-04-01 00:00:00 1899-12-30 02:30:56.000 2008-03-30 02:30:56.000

I tried using the & sign and it advised me of boolean error - also gave me
an error in regards to the Format.


David Benyo via AccessMonster.com said:
Try these and see if one works to your satisfaction:

SELECT NewDate, NewTime, NewDAte & " " & NewTime as DateTime
From dbo.PurgedRecords;

OR this one:

SELECT NewDate, NewTime, Format(NewDAte & " " & NewTime, "mm/dd/yyyy hh:nn
AM/PM") as DateTime
From dbo.PurgedRecords

I'm curious though, if one table has the date and time, but the other one
does not, how do you know what date those records should have?




If I am unable to put the date and the time back together that is fine. The
issue that I am having with that is, I have a table in SQL that is only the
time, it does not carry the 1/1/1900 date. I have another table in SQL that
I need to compare it to and the Time shows with the 1/1/1900 date. Both
fields are set up as date time fields. I'm not sure how to go about making
the 2 fields the same format so that I can match the fields up to run queries.

Any Ideas?
I am trying to pull 2 fields together in SQL. I have tried the following:
Click to expand...
[quoted text clipped - 4 lines]
it was nit he original field. Is there something else that I need to be
doing?
Click to expand...
Click to expand...
Click to expand...
 
This is what I using for the TimeValue - when I type it in my QueryAnalyzer
in SQL it tells me "Incorrect syntax near 'Timevalue'."

Am I missing something?

SElECT Transaction_Time; Timevalue([Transaction_Time]) = SqlTimeField
From dbo.DlyFADfalcon

Klatuu said:
Okay, I see the problem now. SQL Server has a time data type that will carry
only time and not include a data value; however, Jet date/time data type
always carrys a time datevalue. So you can use the TimeValue function to do
the compare. It returns only the time portion of a date/time field.

So it would be something like

Timevalue([MdbDateField) = SqlTimeField
--
Dave Hargis, Microsoft Access MVP


Welthey said:
If I am unable to put the date and the time back together that is fine. The
issue that I am having with that is, I have a table in SQL that is only the
time, it does not carry the 1/1/1900 date. I have another table in SQL that
I need to compare it to and the Time shows with the 1/1/1900 date. Both
fields are set up as date time fields. I'm not sure how to go about making
the 2 fields the same format so that I can match the fields up to run queries.

Any Ideas?
Click to expand...
Click to expand...
 
Back
Top