• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 43
  • Last Modified:

TEXT format date to DATE field in query

Hi,

I have taken over an MSSQL database which, for reasons beyond me, dates have been stored in TEXT fields in the following format yyyy.mm.dd time (2017.11.29 16:27:21).  I am working on an Access Query and need to use this field, but convert it to a date format DD/MM/YYYY so that i can query against the date.  How do i format my query field accordingly?

Many thanks,
Anthony
0
anthonytr
Asked:
anthonytr
1 Solution
 
Ryan ChongCommented:
try create a View in SQL Server in which you convert that text field into date, like:

select ...
cast(yourDate as datetime) as DateField
...

Open in new window


and then let your Access to query to that View instead.
0
 
anthonytrAuthor Commented:
Hi Ryan,

Is there anyway of doing the convert directly in MS Access Query?
0
 
ste5anSenior DeveloperCommented:
What SQL Server version?

I would add a computed, persisted column for this.

Also I would try to get that information. Cause it is a mistake. And it should be corrected, it's a technical debt, which will produce further costs. Thus this is the business reason to correct it.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
aikimarkCommented:
Well, you could do any of the following:
* create a SQL Server view of the table where this field is converted
* similar to above, but using a stored proc
* convert the field in an Access query: cdate(replace([yourdateastextfield],".","-"))
0
 
anthonytrAuthor Commented:
It is a commercial system database from a highly regarded source (I will not name names here - it would be far too embarrassing for them...ha!)

I have created a View in SQL with the correct date format.  When i link this in my Access Database, does the data refresh each time it is opened or queried against?  It doesn't appear like a linked table.

Anthony
0
 
aikimarkCommented:
If you open it in an ADODB recordset, you might be able to control when you see changes.
I think you will always see changes if you open an Access query
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now