Subscribe to this thread
Home - General / All posts - Customize output of CAST when transforming datetime values
dyalsjas120 post(s)
#22-Mar-20 23:52

Working through more Fehily SQL Database Programming examples.

Fehily listing/query 5.10 uses CAST and CONCATENATE to create a defined output string.

I've replicated the Fehily listing/query in Manifold, but the string resulting form CAST ([pubdate] AS NVARCHAR) is particular to Manifold.

When using CAST to transform a datetime value as a text string, is there a way to customize the output text string?

Fehily example output:

Title T12 published on 2000-08-31

Manifold output

Title T12 published on 8/31/2000 0:00:00

tjhb

9,159 post(s)
#23-Mar-20 00:18

Fehily example output--with what DBMS, in what environment?

dyalsjas120 post(s)
#23-Mar-20 02:31

The example output is what Fehily describes as "Standard" SQL. He makes a point to add notations at the end of examples in his text where a listing/query would need to be updated based on DBMS, (+ to concatenate with MS Access versus || for Oracle).

The output from Manifold is completely correct, and reflects the datetime values in the [pubdate] column.

I'm curious if there is a way to reformat the text output by the CAST function so that the concatenated string matches the "Standard" output the Fehily has published.

If I was doing this for a large/persistent project (instead of an exercise), I would probably look at automating a way to export the datetime values to Excel or Access, change them to my desired format and link them back as a text string, thus side-stepping the CAST function.

tjhb

9,159 post(s)
#23-Mar-20 03:04

Yes yes, but where is the shared data for testing?

A link or a download, both fine.

dyalsjas120 post(s)
#23-Mar-20 12:25

Here is my work so far updating the books2.mxb to follow the fifth edition of SQL Programming by Chris Fehily.

I've moved the components into sub-folders by chapter.

I've added a Table Reset query.

I've gotten as far as listing 5.10.

Attachments:
SQL_Programming_Fifth_Ed_Fehily.mxb

lionel

585 post(s)
#25-Mar-20 09:54

i remenber reading a post where formating can be different using different OS setting .

So perhaps have a look to microsoft OS setting to see if change formatting change the way manifold format some content refer to OS setting ?

Attachments:
manifold_date-format.png


!!!!! coronavirus : yes use azithromycine and chloroquine !!!!!

lionel

585 post(s)
#25-Mar-20 10:06

best is i think mix concat many datetime function

Attachments:
concat_mix_DateTime-function.png


!!!!! coronavirus : yes use azithromycine and chloroquine !!!!!

lionel

585 post(s)
#25-Mar-20 10:25

--SQL

SELECT 'Title '+[title_id]+' published on '

  + (CAST(DateTimeYear([pubdate]AS NVARCHAR)

'-' + CAST(DateTimeMonth([pubdate]AS NVARCHAR)

'-' + CAST(DateTimeDay([pubdate]AS NVARCHAR))  AS ["Biography publication dates"]

 

FROM [titles]

WHERE [type] = 'biography' AND [pubdate] IS NOT NULL

ORDER BY [pubdate] DESC;

text

Manifold Documentation

datetime 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

datetimeday 1 2

datetimehour 1 2

datetimemillisecond 1 2

seem to show what is set in OS time level setting

TODO change OS time setting , reboot , reopen test manifold file ...

Attachments:
manifold_cast_time-formating.png
manifold_OS_time_format.png


!!!!! coronavirus : yes use azithromycine and chloroquine !!!!!

dyalsjas120 post(s)
#25-Mar-20 12:34

Lionel, thank you!! Great solution!

I try to remind myself to stay focused on the result, not the bumps in the process.

The result I was looking for was your query, I got distracted by looking at formatting options and didn't get past that bump to consider the various datetime functions.

Not sure an option to customize datetime formatting is something that would rate high on the features list, but your query shows that a little creative thinking can get to the right result.

lionel

585 post(s)
#25-Mar-20 11:49

change date time format in OS level don't change how CAST([pubdate] AS NVARCHAR) return formating.

I test after

-change formating only

-change formating and close session login and login again

-change formating and reboot the OS

regard's


!!!!! coronavirus : yes use azithromycine and chloroquine !!!!!

Manifold User Community Use Agreement Copyright (C) 2007-2019 Manifold Software Limited. All rights reserved.