datetime as TEXT

[james_lankford]

We are using a database that is on sql server.
We didn't make the database.
It is part of a product we bought.
We have no rights to change any part of design in the database, or create tables or anything else.
I can link to the sql server tables through MS Access and run queries.

One of the tables in the sql server database stores a date time value as TEXT in this format:

yyyymmddhhmmsshh

so 2:34:55.01 pm of Feb 28 2011 would look like

'2011022814345501'

I'm currently doing my queries like this

...
where 
(
  datetime_column Between '2010120100000000' And '2010123199999999'

)

to try and get all values for Dec 2010

does anybody see any problem with this ?
It seems to be working, but of course I can't possibly test for all possible date and time values

I'm worried about text not sorting the way I think it will.
Does anybody foresee any problems that may arise with this ?

I was thinking of doing it like this

...
where 
(
  cdbl(datetime_column) Between 2010120100000000 And 2010123199999999
)

that gives the same results as the first way, but like I said I can't possibly test for all possible date and time values

 

[revans]

If you're worried about the text not acting like a date you could create two functions, one to extract the date and another to extract the time from the string. Or, just one to convert the string to a date, whichever approach works best. Then you could create new columns in your queries using those functions.

hth,

Rich

 

[Joe4]

James,

That logic *seems* like it should work to me. I have done similar queries using date as text with Access databases only, and they worked fine.

As long as all the text values are exactly the same format and length, I don't see a problem with it.

 

[xenou]

I think also working would be this (looks more normal to human eyes):

where 
(
  datetime_column >= '20101201' 
  AND
  datetime_column < '20110101'
)

Note that this format isn't (or need not) be so strange. It's close to (or in) ISO-8601 standard format. Some databases use this as the standard format for date storage (e.g., SqlLite). I think its great for international use. Text sorting will put the dates in order correctly since the years are first, then months, then days ... and so in with the least important information at the (right) end of the string.