Single to Date Conversion




 I've been asked to convert an old database made up of fixed length record files. I've deciphered all the fields using a VB program except the date fields. The old docs say the date fields are stored as single (S0) ... my question is: How to I convert these dates to a real date? The data values look like: 930501, 941201, 951130, 960331, 960430, etc. Any thoughts would be greatly appreciated!
Looks like YYMMDD to me. I would try to verify this in the documentation or whatever information source you have on the old database. You could also look for inappropriate numbers like MM not between 1 and 12 and DD not between 1 and 31. If you find any inappropriate numbers than this is the wrong approach. This does not look like a date based on an offset number of days after a start date because the start date would have to be about 2600 years ago.
java.sql.timestamp is a subclass of java.util.date - it should work the same for comparison purposes:

<https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/timestamp.html>

if you specifically need a date, you could convert it back pretty easily; check if isinstance(java.sql.timestamp… [image] akanksha\_varshney:

(convert.tostring(date\_created\_hs.substring(0,10)).equals(convert.tostring(date\_created\_cchq.substring(0,10)))) or (date\_created\_hs <> “”)

hey,

try with the following one,

(convert.tostring(date\_created\_hs.substring(0,10).trim).equals(convert.tostring(date\_created… [@najoua.abbaci](/u/najoua.abbaci)

mabye the flow can be adopted to following:

assign:

leftside: drresult (datatype: datarow)

right side:

dt\_datepv.asenumerable.orderby(function ( r ) datetime.parseexact(r(0).tostring.trim, “dd.mm.yyyy”, system.globalization.cultureinfo.currentuiculture.datetimeformat)).last()

so… let table = base.gettable("table 1")

let record = await input.recordasync("record", table)

await table.updaterecordasync(record.id, {

"single select": null

}) > 
> i want to create ap application which generates popup alerts at the day of event ocurs as well as a day before the event will occur in a specific time interval as well.. events are stored in database and they are all date fields .. so i want to link those fields to create popups .. help me .
> 
> 
> 


In order to help you, you must first provide us with what you have achieved. We do not write code for you, simply correct and enlighten on mistakes. and it's done exactly what you told it to: generate a field that's capable of holding an SQL DATE. Which means a field without a time.  

The correct way of indicating a timestamp using JPA is as follows:



```
   /**
     * Time/date of creation of an entity.
     */
    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "CREATION_DATE")
    protected Date creationDate;

``` Lets say you want to send a table with a single column of GUIDs.


First we need to create a structure using [SqlMetaData](https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.server.sqlmetadata(v=vs.110).aspx) which represents the schema of the table (columns).



```
var tableSchema = new List<SqlMetaData>(1)
{
  new SqlMetaData("Id", SqlDbType.UniqueIdentifier)
}.ToArray();

```

Next you create a list of records that match the schema using [SqlDataRecord](https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.server.sqldatarecord(v=vs.110).aspx).



```
var tableRow = new SqlDataRecord(tableSchema);
tableRow.SetGuid(0, Guid.NewGuid());
var table = new List<SqlDataRecord>(1)
{
  tableRow
};

```

Then create the [SqlParameter](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter(v=vs.110).aspx):



```
var parameter = new SqlParameter();
parameter.SqlDbType = SqlDbType.Structured;
parameter.ParameterName = "@UserIds";
parameter.Value = table;

var parameters = new SqlParameter[1]
{
  parameter
};

```

Then simply call the stored procedure by using the [Database.SqlQuery](https://msdn.microsoft.com/en-us/library/gg696545(v=vs.113).aspx).



```
IEnumerable<ReturnType> result;
using (var myContext = new DbContext())
{
  result = myContext.Database.SqlQuery<User>("GetUsers", parameters)
    .ToList();         // calls the stored procedure
    // ToListAsync();  // Async
{

```

In SQL Server, create your User-Defined Table Type (I suffix them with TTV, Table Typed Value):



```
CREATE TYPE [dbo].[UniqueidentifiersTTV] AS TABLE(
  [Id] [uniqueidentifier] NOT NULL
)
GO

```

Then specify the type as a parameter (don't forget, Table Type Values have to be readonly!):



```
CREATE PROCEDURE [dbo].[GetUsers] (
  @UserIds [UniqueidentifiersTTV] READONLY
) AS
BEGIN
  SET NOCOUNT ON

  SELECT u.* -- Just an example :P
  FROM [dbo].[Users] u
  INNER JOIN @UserIds ids On u.Id = ids.Id
END

``` hi [@ashish.tyagi](/u/ashish.tyagi), hope below solution will work for you

assign

var\_int\_count = list\_mailmessages.where(function(x) convert.todatetime(x.date).tostring(“dd/mm/yyyy”)=date.now.tostring(“dd/mm/yyyy”)).count Use [UNIX\_TIMESTAMP](https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions) function to convert timestamps to seconds, then subtract:



```
select UNIX_TIMESTAMP('20190102 02:14:18','yyyyMMdd HH:mm:ss') -
       UNIX_TIMESTAMP('20190102 00:01:05','yyyyMMdd HH:mm:ss');

```

Returns:


`7993` seconds. 


Difference in `'HH:mm:ss'` format:



```
select from_unixtime(UNIX_TIMESTAMP('20190102 02:14:18','yyyyMMdd HH:mm:ss') -
       UNIX_TIMESTAMP('20190102 00:01:05','yyyyMMdd HH:mm:ss'), 'HH:mm:ss');

```

Returns:



```
02:13:13

``` hi,we have no formal “copy file type and template” from environment test to prod.this is what i’ve done hundreds of times.assuming you created a new file type or copied an existing template. - do this same process in prod (or the other environment), ideally keep the same name and description - just keeps things clean.then update the tabs so the file separator and date formant is the same. if you created any external file template controls update this as well,finally, - in the file template details, i delete all records in the new prod, and then use the copy / past object to copy the details from test to prod. rather than analyzing to see what’s different in the details between test and prod, if we know test works, i would typically delete the details and copy object from test to prod.if you had any formula in test you can manually copy these as well. it’s a few steps, but it only takes a couple minutes. best regards,thomas