CONCATENATING

[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?

 

[KC-Mass]

I believe you are adding the dates, not concatinating. Substitute "&"s for
the "+"s. Of what type are NewDate, NewTime
and what type do you intend for "DateTime" which is I believe a reserved
word in Access?

Regards

Kevin

 

[Klatuu]

--
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.

 

[Welthey]

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?

 

[David Benyo via AccessMonster.com]

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:
[quoted text clipped - 4 lines]
it was nit he original field. Is there something else that I need to be
doing?

 

[Klatuu]

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

 

[Welthey]

I'm trying to write this in SQL and I used the following:

SELECT NewDate, NewTime, NewDAte + " " + NewTime as DateTime

From dbo.PurgedRecords;

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.

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:

[quoted text clipped - 4 lines]

it was nit he original field. Is there something else that I need to be
doing?

 

[Welthey]

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

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

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?

 

[raskew via AccessMonster.com]

Hi-
Not sure what the trailing zeros are meant to represent. Try this (from
debug/immediate window):

newdate = "2008-04-01 00:00:00"
newtime = "1899-12-30 02:30:56"
? datevalue(newdate) + timevalue(newtime)
4/1/2008 2:30:56 AM

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

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

[quoted text clipped - 22 lines]

 

[raskew via AccessMonster.com]

To see how the example in the previous post is stored by Access:

? cdbl(datevalue(newdate))
39539
? cdbl(timevalue(newtime))
0.104814814814815
? cdbl(datevalue(newdate)+ timevalue(newtime))
39539.1048148148

Bob

Hi-
Not sure what the trailing zeros are meant to represent. Try this (from
debug/immediate window):

newdate = "2008-04-01 00:00:00"
newtime = "1899-12-30 02:30:56"
? datevalue(newdate) + timevalue(newtime)
4/1/2008 2:30:56 AM

Bob

This is what I using for the TimeValue - when I type it in my QueryAnalyzer
in SQL it tells me "Incorrect syntax near 'Timevalue'."

[quoted text clipped - 9 lines]