Date time in SQL query

[gmazza76]

Good afternoon,

I am trying to run a query from a data table in Access called "DataRaw" where "Field1" holds the date/time as text showing as
"VALUE=CS:20210809000032361" but I am having an error showing "Syntax error in string in query expression.

Apologies if this is an easy one I am fairly new to SQL

SELECT
format(right(field1,17, 'yyyy/MM/dd, hh:mm:ss) as DateStartTime
from DataRaw

;

thanks in advance
Gavin

 

[Micron]

If you cannot fix the field and its data so that it contains a proper date, I think you will have to use code to extract a date from 20210809000032361. Would only work if the string length and its format is constant. If you don't need the time portion it would be easier. I presume that the time you have there is almost 9:00 AM?

 

[gmazza76]

The split on the txt would be 2021/08/09 is the date portion and the rest is hh:mm:ss.ss

I have used sql before to do this in an sql server, but never in Access and I am having a few issues.

Thanks

 

[Micron]

You could modify this to suit the order of the year, month, day elements as you require (I have to get ready for a dental appt). Your query calculated field would call this function for every record; not sure if that will be an issue. For that you pass the field containing the string value in the function call. Don't know what data type you require, but this will return a string. Don't know if the CS: part is in the field or not (I assumed not). If it is, just modify the numbers passed to Mid function, but Left will have to be replaced with Mid to eliminate the CS:

Example, calc field is like
TheDateTime: ExtractTime([name of field containing the values])

Function ExtractTime(TimeIn As String) As String
Dim strYear As String, strMonth As String, strDay As String, strTime As String

strYear = Left(TimeIn, 4)
strMonth = Mid(TimeIn, 5, 2)
strDay = Mid(TimeIn, 7, 2)
strTime = Mid(TimeIn, 9)
ExtractTime = DateSerial(strYear, strMonth, strDay) & " " & TimeSerial(0, 0, strTime)
ExtractTime = Format(ExtractTime, "mm/dd/yyyy hh:mm:ss AMPM")
End Function

Perhaps something here will allow you to get your simpler attempt to work (e.g. the DateSerial, Format & TimeSerial functions).

 

[james_lankford]

first: you're missing a )
format(right(field1,17, 'yyyy/MM/dd, hh:mm:ss) as DateStartTime
should be
format(right(field1,17), 'yyyy/MM/dd, hh:mm:ss) as DateStartTime

second:
i don't know if that will fix it because your example is
2021 08 09 00 00 32 361
yyyy mm dd hh mm ss mmm
the last 3 m's are milliseconds

so maybe you don't want those last 3 characters
maybe you want
format(mid(field1, 4, 14), 'yyyy/MM/dd, hh:mm:ss) as DateStartTime

at least I think that's the syntax for mid
mid is basically substring

but Micron's code is probably a a better way

 

[Micron]

32,361 is the number of seconds elapsed since midnight. There are 86,400 seconds in a day.
So that represents .3745 of a day.

 

[james_lankford]

32,361 is the number of seconds elapsed since midnight. There are 86,400 seconds in a day.
So that represents .3745 of a day.

I don't believe so in this case
if 32,361 is the number of seconds since midnight then what does the 00 00 below represent ?
2021 08 09 00 00 32 361
I'm thinking they are hours and minutes
you wouldn't need them if you were just counting seconds since midnight

datetime (Transact-SQL) - SQL Server

datetime defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock.

docs.microsoft.com

YYYY is four digits from 1753 through 9999 that represent a year.

MM is two digits, ranging from 01 to 12, that represent a month in the specified year.

DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month.

hh is two digits, ranging from 00 to 23, that represent the hour.

mm is two digits, ranging from 00 to 59, that represent the minute.

ss is two digits, ranging from 00 to 59, that represent the second.

n* is zero to three digits, ranging from 0 to 999, that represent the fractional seconds.

 

[Micron]

if 32,361 is the number of seconds since midnight then what does the 00 00 below represent ?

Don't know; it's not my data. You might be correct, but then the original expression is taking 17 characters (20210809000032361) and expecting to get a time value from that as well. The time portion would then be 000032361, which could be as you say, or it could be a time stamp in seconds from a starting point. I once did that in a multi user db using fractional seconds because I needed a time stamp that would virtually guarantee that no two people could create a record even in the same second. It could also be that if 000032361 became 000086400, the next second would make the value 000100000, or 1 day and 0 seconds (not that that would make a whole lot of sense to me, but it's not my system).
All speculation of course, and could be easily cleared up if gmazza76 cares to do so.