• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1378
  • Last Modified:

converting a date that is in INT format in SQL to standard date format xx/xx/xxxx

I have a SQL server DB on the backend and a program that uses it written in Clarion 5 on the front end. Problem is Clarion is writing dates and times as date type INT (length 4) and I am now using MS Access 2003 for reports and some queries and I don't know where and how to convert the data. For example, the data is as follows: date format= 74300, and the time format= 3578973. The programmer I used suggested the following code: CAST(PMT_DATE - 36163 AS datetime). Problem is, I have no clue where to put it or how to execute the code. Any help?
0
buzcut
Asked:
buzcut
  • 3
  • 3
1 Solution
 
Jay ToopsCommented:
the easiest way would be to create a view on SQL server
a view is just an alternate way of looking at data from a sql server table
then in access simply point your query to this view instead of the main table.

CREATE view MyDateView as
select CAST(PMT_DATE - 36163 AS datetime) as PMT_DATE, field2, field3
FROM MYTABLE

Jay
0
 
RaisinJCommented:
Is your program in Clarion just writing to the DB or also reading data?
0
 
buzcutAuthor Commented:
the front end is written in Clarion and it reads and writes data to the SQL Server tables.
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
Jay ToopsCommented:
buzcut... have you tried the view method?

Jay
0
 
buzcutAuthor Commented:
Jay-

your instructions worked great! I can see the regular date now... but the other part of this question was how do I create a new table based on the new view? See, I want to have the table store the date as datetime and not as an INT so I can continue to work with the DB from Access, since I am replacing the Clarion front end. Thanks again!!!

Ed
0
 
buzcutAuthor Commented:
Jay-

I just figured out how to create a table with the new definitions and converted data. Again, thank you for all of your help!!!

Ed
0
 
Jay ToopsCommented:
Cool

Enjoy :)
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

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