Welcome Guest! Log in

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.

Please Log in or Create an account to join the conversation.