Welcome Guest! Log in
×

Notice

The forum is in read only mode.
Stambia versions 2.x, 3.x, S17, S18, S19 and S20 are reaching End of Support January, 15th, 2024. Please consider upgrading to the supported Semarchy xDI versions. See Global Policy Support and the Semarchy Documentation.

The Stambia User Community is moving to Semarchy! All the applicable resources have already been moved or are currently being moved to their new location. Read more…

Topic-icon Idea Off Topic : Little tips for manipulating string and date in SybaseIQ

  • Nicolas Verscheure
  • Nicolas Verscheure's Avatar Topic Author
  • Offline
More
22 Nov 2014 18:52 - 22 Nov 2014 18:53 #1 by Nicolas Verscheure
Some usefull commands for SybaseIQ database :

To get the current date localized (for French : DD/MM/YYYY format) : today()

To get the current date with hours : getdate()

To extract a part of a string : substr(fieldName, startPosition, length)

To find an element in a string : locate(fieldName, 'string to locate')
Same but starting by the end : locate(fieldName, 'string to locate', -1)

To get the length of a string or the length of the value of a field :
length(fieldName)
length('string')

To get the date in a specific format :

select convert(char( 8 ), getdate(), 112)
  • char( 8 ) the size of the output value expected
  • getdate() the date value to convert
  • 112 is an option for pre-defined format in this example : yyyymmdd

Pre-defined format options :

0 mon dd yyyy hh:mi AM (or PM)
1 mm/dd/yy
2 yy.mm.dd
3 dd/mm/yy
4 yy.mm.dd
5 dd-mm-yy
6 dd mon yy
7 mon dd, yy
8 hh:mm:ss
9 mon dd yyyy hh:mi:ss:mmmAM (or PM)
10 mm-dd-yy
11 yy/mm/dd
12 yymmdd
100 mon dd yyyy hh:miAM (or PM)
101 mm/dd/yyyy
102 yyyy.mm.dd
103 dd/mm/yyyy
104 yyyy.mm.dd
105 dd-mm-yyyy
106 dd mon yyyy
107 mon dd, yyyy
108 hh:mm:ss
109 mon dd yyyy hh:mi:ss:mmmAM (or PM)
110 mm-dd-yyyy
111 yyyy/mm/dd
112 yyyymmdd
Last edit: 22 Nov 2014 18:53 by Nicolas Verscheure.