CODEWITHSUNDEEP
datetimetimestampsybasesap-ase
max
max

Reputation: 59

Sybase: DateTime to char (timestamp)

I have to convert getdate() TO char I was using below to set in order to insert the timestamp of batch process set a_batch_id = (select convert(char(12), getdate(), 12)) I would get 160309 for 03/09/16 but what i need is 39201644418486 while getdate() fetches 3/9/2016 4:44:18.486 PM

I am not worried about the order yymmdd but is it possible to add the hours and minutes as mentioned above.

anything close to 160309444 should be fine, with seconds even better.

Upvotes: 1

Views: 6670

Answers (1)

DRapp
DRapp

Reputation: 48169

From researching, it appears you are running Sybase, and you do not want a typical YYYYMMDD type of formatting, but mmddyyyyhhMIss Look at Sybase Date Conversions

convert(char(32), getdate(), 139 )

which would give the format of 

"mmm dd yy[yy] hh:nn:ss.ssssss"

Then, you would wrap that in another replace command to strip out the unwanted. In this case, removal of space, colon and decimal point...

replace( replace( replace( convert(char(32), getdate(), 139 ), " ", "" ), ":", "" ), ".", "" )

FEEDBACK

With respect to using this as a "batch id", consider having auto-increment columns as an INTERNAL ID to help keep joins to foreign keys within database clear. If you want to have an alternate "key" lookup, such as WHEN, or search criteria, yes, add that to the "Batch" table. What happens if you want some other identifier. Your internal IDs would have no impact. Having a date/time stamp on a "batch" table would allow you to query, sort, group by in reports too.

Looking more into the Sybase documentation, this too might work for you as "mmm" returns the character month. This is longer, but should work for you. The year will return 4 digits, so I did not have to explicitly pre-add a leading zero and then take the right side.

ex: if the month was 1, it would create "01". ex: if month was 11, it would create "011" and thus the right 2 = "11"

Similarly for Day, Hour, Minute, Second.

select
     right( '0' + trim( str( month( getdate() ))), 2 )
   + right( '0' + trim( str( day( getdate()))), 2 )
   + right(       trim( str( year( getdate() ))), 4 )
   + right( '0' + trim( str( hour( getdate()))), 2 )
   + right( '0' + trim( str( minute( getdate()))), 2 )
   + right( '0' + trim( str( second( getdate()))), 2 )

Now, if this gets you the most of what you need, great. Now, if you need that millisecond value, just add on one more + component for below, but test and adjust the position depending on how sybase works the return value.

+ substring( convert( varchar, getdate(), 37 ), charindex( convert( varchar, getdate(), 37 ), '.' ), 4)

Since the convert mode 37 returns " hh:nn:ss.ssssss" but no guarantee if single or double-digit value for the hour, minute, seconds (that I can test), I am looking for the '.' as the position offset to start the substring and getting only the milliseconds. If the decimal is included, change the charindex() to charIndex() +1 so it is removed from the returned string.

Upvotes: 1

Related Questions